我在一个表中有一个带有多个产品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/

10-12 07:35
查看更多