欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql关联更新update

程序员文章站 2022-06-01 17:19:52
...

https://blog.csdn.net/babyfish13/article/details/78082844

***************************************************

样式1:

update xcs_user_credit_score a1,xcs_user_credit_score a2
   set a1.user_currday_score=a1.user_currday_increment_score+a2.user_currday_score
 where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'
   and a1.uid=a2.uid;

样式2:

    update xcs_user_credit_score a1
     inner join xcs_user_credit_score a2
        on a1.uid=a2.uid
       set a1.user_currday_score=a1.user_currday_increment_score+a2.user_currday_score
     where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'
    ;

突然发现mysql的一些sql语法也很神奇,像这样式的sql语句,在oracle中是行不通的,需要其他的形式。
另外的几个例子:

    --sub:
    update xcs_user_credit_score a1,xcs_user_credit_score a2
       set a1.user_currday_score=(case when a2.user_currday_score-10>0 then a2.user_currday_score-10 else 0 end)
     where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'
       and a1.uid=a2.uid
       and (a1.seqing_score=0 and a1.user_silent_score=0 and a1.jubao_score=0 and a1.chengpaopao_score=0 and a1.weifan_score=0 and a1.upper_manage_score=0 and a1.pay_active_score=0 and a1.view_active_score=0 and a1.message_active_score=0 and a1.gift_active_score=0 and a1.live_active_score=0)
       and a2.user_currday_score>0
    ;
     
    --plus:
    update xcs_user_credit_score a1,xcs_user_credit_score a2
       set a1.user_currday_score=(case when a1.user_currday_score+10<0 then a1.user_currday_score+10 else 0 end)
     where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'
       and a1.uid=a2.uid
       and (a1.seqing_score=0 and a1.user_silent_score=0 and a1.jubao_score=0 and a1.chengpaopao_score=0 and a1.weifan_score=0)
       and a1.user_currday_score<0
    ;

 

相关标签: mysql sql