sql-server - 运行 MSsql 查询类似 foreach

Question

我有两个表，一个名为 Product，另一个名为 ProductVariant。 ProductVariant 具有来自 Product 的 ID。我需要对两个表进行简单的操作。

这是我提出的查询:

declare @id int

declare cur CURSOR LOCAL for
select @id = ProductID  from Product WHERE (Published = '0')

open cur

fetch next from cur into @id 

while @@FETCH_STATUS = 0 BEGIN

UPDATE Productvariant SET Cost = SalePrice WHERE VariantID = @id;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE VariantID = @id; 

fetch next from cur into @id 
END

close cur
deallocate cur

但它给了我: 消息 154，级别 15，状态 3，第 4 行游标声明中不允许变量赋值。

谢谢

Answer 1

你可以尝试类似的东西:

UPDATE ProductVariant SET Cost =SalePrice , SalePrice = 0.00
WHERE VariantID IN (SELECT productID FROM Product WHERE Published = '0')