mysql关联更新update

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/BabyFish13/article/details/78082844

样式1：

1. update xcs_user_credit_score a1,xcs_user_credit_score a2

2. set a1.user_currday_score=a1.user_currday_increment_score+a2.user_currday_score

3. where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'

4. and a1.uid=a2.uid;

样式2：

1. update xcs_user_credit_score a1

2. inner join xcs_user_credit_score a2

3. on a1.uid=a2.uid

4. set a1.user_currday_score=a1.user_currday_increment_score+a2.user_currday_score

5. where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'

6. ;

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

1. --sub:

2. update xcs_user_credit_score a1,xcs_user_credit_score a2

3. set a1.user_currday_score=(case when a2.user_currday_score-10>0 then a2.user_currday_score-10 else 0 end)

4. where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'

5. and a1.uid=a2.uid

6. 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)

7. and a2.user_currday_score>0

8. ;

9.  

10. --plus:

11. update xcs_user_credit_score a1,xcs_user_credit_score a2

12. set a1.user_currday_score=(case when a1.user_currday_score+10<0 then a1.user_currday_score+10 else 0 end)

13. where a1.pt_day='2017-09-20' and a2.pt_day='2017-09-19'

14. and a1.uid=a2.uid

15. 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)

16. and a1.user_currday_score<0

17. ;

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