我在一个表中有一个带有多个产品ID的函数。我要执行此更新并将这些变量设置为所需的产品ID。现在,这只会更新我的函数中的第一个productID,该函数将拆分变量的多个值。我知道CTE在这里是理想的选择,但是我在语法方面并不出色。
我想要的结果是对于@CSV中的每个值,执行更新语句。任何帮助都会很棒。
DECLARE
@MaxAllowance INT = 25,
@AllowanceType INT = 2,
@AllowancePeriod INT = 2,
@Account_ID INT = 13379,
@CSV VARCHAR(50) = '506,280,281,282,286',
@count INT
DECLARE @DiscountClass_ID INT =
(
SELECT DiscountClass_ID FROM Account WHERE Account_ID = @Account_ID
);
SELECT @count =
(
SELECT COUNT(*) FROM dbo.FN_csv_tolist(@CSV)
);
declare @ProductID int
declare @rn int
declare ItemCursor cursor local static forward_only read_only for
select Value,
row_number() over(partition by Value order by Value) as rn
FROM dbo.FN_csv_tolist(@CSV)
open ItemCursor
fetch next from ItemCursor
into @ProductID, @rn
WHILE @count > 0
begin
update dbo.ProdTerm
SET MaxAllowance = @MaxAllowance,
AllowanceType = @AllowanceType,
AllowancePeriod = @AllowancePeriod
WHERE Product_ID = @ProductID
SET @count = @count - 1;
fetch next from ItemCursor
into @ProductID, @rn
end
close ItemCursor
deallocate ItemCursor
最佳答案
我对您的代码有些困惑,我不确定需要游标的原因,因为代码可能要简单得多。一种方法是:
DECLARE @MaxAllowance INT = 25,
@AllowanceType INT = 2,
@AllowancePeriod INT = 2,
@CSV VARCHAR(50) = '506,280,281,282,286';
WITH Test
AS
(
SELECT [Value]
FROM dbo.FN_csv_tolist(@CSV)
)
UPDATE PT
SET MaxAllowance = @MaxAllowance,
AllowanceType = @AllowanceType,
AllowancePeriod = @AllowancePeriod
FROM dbo.ProdTerm AS PT
INNER JOIN Test AS T ON PT.Product_ID = T.Value;
关于mysql - 从具有多行数据的表更新表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/59862046/