drgm51600 2019-07-27 03:51
浏览 66
已采纳

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

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 ?
  • 写回答

1条回答 默认 最新

  • duanchao9559 2019-07-27 07:12
    关注

    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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 workstation加载centos进入emergency模式,查看日志报警如图,怎样解决呢?
  • ¥50 如何用单纯形法寻优不能精准找不到给定的参数,并联机构误差识别,给定误差有7个?matlab
  • ¥15 workstation加载centos进入emergency模式,查看日志报警如图,没有XFS,怎样解决呢?
  • ¥15 应用商店如何检测在架应用内容是否违规?
  • ¥15 Ubuntu系统配置PX4
  • ¥50 nw.js调用activex
  • ¥15 数据库获取信息反馈出错,直接查询了ref字段并且还使用了User文档的_id而不是自己的
  • ¥15 将安全信息用到以下对象时发生以下错误:c:dumpstack.log.tmp 另一个程序正在使用此文件,因此无法访问
  • ¥15 速度位置规划实现精确定位的问题
  • ¥15 MAC虚拟机(win11)USB插上后无串口com,无法烧录