如何使用游标更新SQL中的多行?

Question

下面是我正在尝试但不起作用的SQL更新。但它适用于单个记录整数更新，同时使用 @sampids 作为 INT 数据类型。任何帮助将不胜感激。谢谢

DECLARE @sampids AS NVARCHAR(1000)='10,20,30'   
DECLARE @sampcursorno AS INT=0
DECLARE sample_cursor CURSOR FOR
SELECT VALUE FROM Split(@sampids,',')
OPEN sample_cursor
FETCH NEXT FROM sample_cursor INTO @sampcursorno
WHILE @@FETCH_STATUS = 0
BEGIN   
UPDATE tbl_Testing
SET SampId = @sampcursorno
FETCH NEXT FROM sample_cursor INTO @sampcursorno
END
CLOSE sample_cursor
DEALLOCATE sample_cursor

Answer 1

我知道您想更新原始表，将每个sampId替换为输入 csv 列表中的每个相应索引。

SQL Server 中没有split()函数。您不能使用string_split()，因为它不能保证零件返回的顺序。

一种选择是使用递归查询拆分输入字符串，然后使用生成的数据集进行更新：

declare @sampids as nvarchar(1000)='10,20,30';
with cte as (
select 
1 id,
cast(substring(@sampids, 1, charindex(',', @sampids) - 1) as int) sampid,
substring(@sampids + ',', charindex(',', @sampids) + 1, len(@sampids)) rest
union all
select 
id + 1,
cast(substring(rest, 1, charindex(',', rest) - 1) as int),
substring(rest, charindex(',', rest) + 1, len(rest))
from cte
where charindex(',', rest) > 0
)
update t
set sampid = c.id
from tbl_Testing t
inner join cte c on c.sampid = t.sampid