关于多表同时更新的问题
请教下面的语句为什么不能执行？
是否一个更新语句一次只能更新一张表呢？？
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)
