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

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

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)