Looping in TSQL and update statement not works

Question

How do i split delimited string TSQL and go through a loop (while or for) and update a table column based on split values

Basically dbo.omega needs to updated for the column latest_version based on values from @source

declare @source [varchar](500)
begin 
set @source = "employee:0,dept:1" # it is comma delimited string 

 #  I want to write logic for these two updates in a single update stament by looping through @source 
 #UPDATE dbo.omega set latest_version = 0 where obj_name = 'employee'
 #UPDATE dbo.omega set latest_version = 1 where obj_name = 'dept'

 splitarr = @source.split(",")
 i = 0
 while(i < len(splitarr)):
  UPDATE dbo.omega set latest_version = splitarr(i)
  i =i+1 
 end

[Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) As for the remainder of what you're asking, it's completely unclear without sample data and expected results. — Thom A, Aug 17 '23 at 11:21
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Aug 17 '23 at 13:21

score 2 · Accepted Answer · answered Aug 17 '23 at 12:01

You can use STRING_SPLIT to split up the string, then CHARINDEX and SUBSTRING in an APPLY to get the two halves, finally joining all that to omega and updating.

DECLARE @source varchar(max) = 'employee:0,dept:1';

UPDATE o
SET latest_version = v2.value
FROM STRING_SPLIT(@source, ',') s
CROSS APPLY (VALUES(
    NULLIF(CHARINDEX(':', s.value), 0)
)) v1(colon)
CROSS APPLY (VALUES(
    SUBSTRING(s.value, 1, v1.colon - 1),
    TRY_CAST(SUBSTRING(s.value, v1.colon + 1, LEN(s.value)) AS int)
)) v2(name, value)
JOIN omega o ON o.obj_name = v2.name
WHERE s.value IS NOT NULL;

It would be much better if you just used a Table Valued Parameter or a table variable

DECLARE @source TABLE (name varchar(50) PRIMARY KEY, value int NOT NULL);
INSERT @source VALUES
('employee', 0),
('dept', 1);

UPDATE o
SET latest_version = s.value
FROM @source s
JOIN omega o ON o.obj_name = s.name;

Your answer saved my four hours of time , thank you . i also understood your query step by step, it will be useful for me in future requirements as well — Surender Raja, Aug 17 '23 at 18:03

Looping in TSQL and update statement not works

1 Answers1