关于联合的SQL更新语句

如何用一个SQL语句将两个结构完全相同的表其中的一个字段求和去更新第三个表中的一个字段?
三个表的主键都是:invno
比如两个表名是:table1,table2
求和的字段名是:amount
更新的表是:table3
更新的字段是:usd
---------------------------------------------------------------

update table3 set usd=t1.amount+t2.amount
from table3 t3 inner join table2 t2 on t3.invno=t2.invno inner join table1 t1 on t1.invno=t2.invno

---------------------------------------------------------------

试一下这样:
select invno,sum(amount) as amount into #Temp from (
select invno,amount from Table1
union all
select invno,amount from Table2
) t1

update table3 set usd=t2.amount
from table3 t3,#Temp t2
where t3.invno=t2.invno

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus