把select结果update更新到表中,从查询结果中更新数据

时间:2020-07-05 21:55:30   收藏:0   阅读:581

mysql

update dw.agt_acco_balance a inner join (
select 
    account_num,
    sum(actual_recharge_amount) as sum,
    count(*) 
from ods.sl_topup_00000000001 b
inner join dw.agt_acco_balance c 
on b.account_num=c.acco_num
group by b.account_num
) as temp
on a.acco_num=temp.account_num
set a.balance=a.balance+temp.sum;

 

PostgreSQL

update dw.agt_acco_balance a
set balance=balance+temp.sum
from (
select 
    account_num,
    sum(actual_recharge_amount) as sum,
    count(*) 
from ods.sl_topup_00000000001 b
inner join dw.agt_acco_balance c 
on b.account_num=c.acco_num
group by b.account_num
) as temp
where a.acco_num=temp.account_num;

技术分享图片

 

 

参考:

  1)mysql 把select结果update更新到表中,从查询结果中更新数据

  2) mysql和Postgresql通过查询进行更新区别

原文:https://www.cnblogs.com/xy-ouyang/p/13252002.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!