update更新一张表里不同的数据怎么写？

[魔拉宝]

有一个表名为：substock，表结构和数据如下：

stockid goodsid amount
001 123456 4
002 123456 6
002 111111 10
002 222222 18
001 222222 0
001 111111 2


怎么写update命令使stockid为002的数量累加到001的相同的goodsid里，而002所有的记录amount都为0。

也就是执行之后如果如下：
stockid goodsid amount
001 123456 10
002 123456 0
002 111111 0
002 222222 0
001 222222 18
001 111111 12


请高手列出SQL代码。在线等。多谢！

[魔拉宝]

有没有高手啊？

[魔拉宝]

update substock set amount=b.amount+a.amount from substock b,a where b.stockid='01' and a.goodsid=b.goodsid

这个代码老是提示出错！

[一把编程的菜刀]

update substock set amount=b.amount+amount from (select * from substock where stockid='002') b where stockid='001' and goodsid=b.goodsid

试试

[hongmei85]

试下
update t1 set amount = (select sum(amount) from substock bb where bb.goodsid= t1.goodsid) from substock t1 where stockid='001'
update t1 set amount = 0 from substock t1 where t1.stockid<>'001' and exists(select 1 from substock cc where cc.stockid='001' and cc.goodsid=t1.goodsid)

[多面木头]

update a set a.amount=b.amount+a.amount 
from (select * from substock where stockid='002') b ,substock a
where a.stockid='001' and a.goodsid=b.goodsid

只能加上，怎么置为零，还得再写一句