关于多表同时更新的问题
请教下面的语句为什么不能执行？

是否一个更新语句一次只能更新一张表呢？？

DECLARE   @OldMaterialCode   VARCHAR(50)
DECLARE   @NewMaterialCode   VARCHAR(50)

SET   @OldMaterialCode   =   '209016011 '
SET   @NewMaterialCode   =   '209015004 '

UPDATE   a,b
SET   a.StorageAmount   =   b.StorageAmount,   b.StorageAmount   =   a.StorageAmount,
a.PlanPrice   =   b.PlanPrice,   b.PlanPrice   =   a.PlanPrice
FROM   MMS_SHAccountCardInfo   a,   MMS_SHAccountCardInfo   b
WHERE   a.AccountCardCode   LIKE   '% '+@NewMaterialCode+ '% '
AND   b.AccountCardCode   LIKE   '% '+@OldMaterialCode+ '% '
AND   LEFT(a.AccountCardCode,   6)   =   LEFT(b.AccountCardCode,   6)



------解决方案--------------------
更新语句一次只能更新一张表
------解决方案--------------------
可以使用触发器来解决.

------解决方案--------------------
分開寫成兩個update語句跟新兩個表不就好了
------解决方案--------------------
UPDATE a
SET a.StorageAmount = b.StorageAmount,
a.PlanPrice = b.PlanPrice
FROM MMS_SHAccountCardInfo a, MMS_SHAccountCardInfo b
WHERE a.AccountCardCode LIKE '% '+@NewMaterialCode+ '% '
AND b.AccountCardCode LIKE '% '+@OldMaterialCode+ '% '
AND LEFT(a.AccountCardCode, 6) = LEFT(b.AccountCardCode, 6)

UPDATE b
SET b.StorageAmount = a.StorageAmount,
b.PlanPrice = a.PlanPrice
FROM MMS_SHAccountCardInfo a, MMS_SHAccountCardInfo b
WHERE a.AccountCardCode LIKE '% '+@NewMaterialCode+ '% '
AND b.AccountCardCode LIKE '% '+@OldMaterialCode+ '% '
AND LEFT(a.AccountCardCode, 6) = LEFT(b.AccountCardCode, 6)