How to Update multiple rows in SQL using a CURSOR?

Question

Below is my SQL update that i was trying and didnt work. But it worked for a single record integer update while using @sampids as INT data type. Any help would be much appreciated. Thanks

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

Your update doesn't have a where clause. So each iteration of the cursor ALL rows are updated. When the cursor is finished all rows will be updated with whatever value came last (you also have no control over that, btw.). — HoneyBadger
– HoneyBadger, Commented Jun 3, 2020 at 12:56
Also, please edit your question to include that expected output, it's very hard to read in a comment — HoneyBadger
– HoneyBadger, Commented Jun 3, 2020 at 19:27

GMB · Accepted Answer · 2020-06-03 12:53:59Z

I understand that you want to update the original table, replacing each sampId with each respective index in the input csv list.

There is no split() function in SQL Server. You can't use string_split(), because it does not guarantee the order in which parts are returned.

One option is to split the input string with a recursive query, and then use the resulting dataset for update:

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

Collectives™ on Stack Overflow

How to Update multiple rows in SQL using a CURSOR?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related