把select结果update更新到表中,从查询结果中更新数据
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;
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 and c.source='sl_account_00000000001' group by b.account_num ) as temp where a.acco_num=temp.account_num and a.source='sl_account_00000000001';
参考:
posted on 2020-07-05 21:39 wenbin_ouyang 阅读(2401) 评论(0) 编辑 收藏 举报