drgm51600 2019-07-26 19: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-26 23: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)
    

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥100 求一个c#通过CH341读取数据的Demo,能够读取指定地址值的功能
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
  • ¥40 selenium访问信用中国
  • ¥20 在搭建fabric网络过程中遇到“无法使用新的生命周期”的报错
  • ¥15 Python中关于代码运行报错的问题
  • ¥500 python 的API,有酬谢
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部