请问这个SQL语句应该如何写？

[myperry]

table1有3个字段（aaa,bbb,cc1）其中aaa是主键
aaa bbb cc1
1001 my1 60
1002 my2 100
1003 my4 50
...
1009 my7 930

table2有3个字段（ddd,aaa,cc2）其中ddd是主键
ddd aaa cc2
001 1001 40
002 1001 20
003 1002 15
004 1002 30
005 1002 55
006 1003 50
...
014 1009 330
015 1009 600

现在我想用table2表中的cc2字段的分组合计值（按aaa分组）来更新table1表中对应的cc1值。
（也就是60 = 40+20 100 = 15+30+55 50 = 50 930 = 330+600）
请问这个SQL语句应该如何写？

[playyuer]

update table1
set cc1= (select sum(cc2) from table2 where aaa = table1.bbb)

[playyuer]

update table1
set cc1= (select sum(cc2) from table2 where aaa = table1.aaa)

[yujohny]

update A
set A.cc1=ISNULL(B.cc,0)
from table1 A LEFT JOIN
(select aaa,sum(cc2) as cc from table2 group by aaa) B
on A.aaa=B.aaa