update a set totalnum=abs(a.totalnum-b.totalnum) from a a,b b where a.ckh=b.ckh and a.dm=b.dm and a.mc=b.mc
是的左连接可以,但是不能a-b(关系运算),先select减
看错了,是要视图阿,sorry支持楼上的
谢谢各位的回答,都不对呀,其实在我的A,B表中是没有二条记录的ckh,mc,dm相等的,因为我已经group by 过了的,而且A,B两表都是视图的,所以也不可能用update了
select A.ckh,A.dm,A.mc, isnull(B.totalnum,0)-isnull(C.totalnum) as totalnum from (select ckh,dm,mc from a union select ckh,dm,mc from b ) A left join a B on A.chk=B.chk and A.dm=B.dm and A.mc=B.mc left join b C on A.chk=C.chk and A.dm=C.dm and A.mc=C.mc
set totalnum=abs(a.totalnum-b.totalnum)
from a a,b b
where a.ckh=b.ckh and a.dm=b.dm and a.mc=b.mc
isnull(B.totalnum,0)-isnull(C.totalnum) as totalnum
from (select ckh,dm,mc from a
union
select ckh,dm,mc from b ) A
left join a B on A.chk=B.chk and A.dm=B.dm and A.mc=B.mc
left join b C on A.chk=C.chk and A.dm=C.dm and A.mc=C.mc