如何在触发器中将值从一个查询传递到另一个查询

Question

I need to understand how to do the following : If a user has the diffPoints field changed (was 10 and became 20), then increment all upoints fields of all other users in the same group(s) as this user with the new value of diffPoints. I think I will do it via a trigger. So first, a trigger will listen to any change in the "diffPoints" field in the users table. second, I will get all users that are in the same group(s) as the specified user_id. I will do this by doing a query to get the groups in which the user is a member, and then use this result (i.e. groups) to get the other users in those groups via another query. And at last, I will update the upoints field for all these users.

1. First Query to get the diffPoints of a user :

select diffPoints 
from
users
where
id=6

1. Second query to get all groups where this user exist :

select gp1.group_id 
from 
grpusrs as gp1
where  
gp1.user_id = 6

1. Third query to get the users that are in the same group

SELECT
gp2.user_id
FROM
grpusrs as gp2
WHERE
gp2.group_id = gp1.group_id

1. Fourth query to update the upoints for all user_ids in query 3 :

UPDATE users increment upoints = (diffPoints in query1) WHERE users.id = (user_id from query 3)

Please advise in the following :

• I need to know if the trigger solution is possible for this scenario.
• Can I pass values between queries ?
• Is there a better way to combine query 1,2,3 ?
• What is the correct syntax for incrementing in query4 ?

Answer 1

You can do this in one multi table update statement (see https://dev.mysql.com/doc/refman/8.0/en/update.html)

given

drop table if exists us,ug;

create table us
(id int ,diffpoints int);
create table ug
(id int, uid int);

insert into us values
(1,10),(2,20),(3,30),(4,40),(5,50),(6,50);

insert into ug values
(1,1), (2,2),(2,3),(5,5),
(6,2), (6,6);

update us  join
(
select us.id,ug.id grp
from us
join ug on ug.uid = us.id
where ug.id in
(select ug.id 
from us
join ug on ug.uid = us.id and us.id = 2
) 
) j on j.id = us.id
set diffpoints = 100;

Where the join works out all the users to be updated

+------+------------+
| id   | diffpoints |
+------+------------+
|    1 |         10 |
|    2 |        100 |
|    3 |        100 |
|    4 |         40 |
|    5 |         50 |
|    6 |        100 |
+------+------------+
6 rows in set (0.00 sec)