SQL多表关联更新

[dream1205]

现有两个表结构如下:
A: A1 A2 A3
1 A 10
2 B 10
B: B1 B2 B3
1 A 3
1 A 2
2 B 7
A表中A1,A2列与B表中B1,B2列相等的行中,用A表的A3减去B表中B3的总和,将得到的值更新到A表的A3列了.
请问这样的SQL语句应该如何写呀??

[-狙击手-]

update a
set a.a3 = a.a3 - isnull( b.b3,0)
from ta a
left join (select b1,b2,sum(b3) as b3 from tb group by b1,b2 ) b
on a.a1 = b.b1 and a.a2 = b.b2

[子陌红尘]

update A set A3=A3-isnull((select sum(B3) from B where B1=A.A1 and B2=A.A2),0)

[ojuju10]

create table aa(a1 int,a2 varchar(10),a3 int)
insert into aa select 1,'a',10
insert into aa select 2,'b',10

create table bb(b1 int,b2 varchar(10),b3 int)
insert into bb select 1,'a',3
insert into bb select 1,'a',2
insert into bb select 2,'b',7


update a set a3=a3-b3 from aa a,(select b1,b2,sum(b3) b3 from bb group by b1,b2) b
where a.a1=b.b1 and a.a2=b.b2

select * from aa

a1          a2         a3          
----------- ---------- ----------- 
1           a          5
2           b          3

（所影响的行数为 2 行）

[dream1205]

可以了，谢谢！