How do I update rows using while loop?

Question

I'm trying to update the SQL rows where the update query is located in the while loop.

DECLARE @Sayac INT
SET @Sayac= (select count(ABBREVIATION) from [MSTR_MD].[dbo].[v_OpcoGeneral_UserList])
WHILE (@Sayac>0)
BEGIN
    PRINT @Sayac;
    (select ABBREVIATION FROM  [MSTR_MD].[dbo].[v_OpcoGeneral_UserList] WHERE [MSTR_MD].[dbo].[v_OpcoGeneral_UserList].ID = @Sayac)
 UPDATE [dwh].[dbo].[opco_securty]
 SET opco_general = REPLACE(opco_general, (select ABBREVIATION FROM  [MSTR_MD].[dbo].[v_OpcoGeneral_UserList] WHERE [MSTR_MD].[dbo].[v_OpcoGeneral_UserList].ID = @Sayac), '')
    SET @Sayac = @Sayac - 1

END

But in this query, it doesn't work as expected. I guess the while loop is not being compiled like in programming languages. Could you please recommend a solution? Thanks!

Basically for instance, if "Ugurcan.Kaya" is received from the view, remove "Ugurcan.Kaya" from the opco_general (text) column on the opco_securty table.

The intended logic looks strange. Why would you want to compare a count with the id column? That feels like a mistake. Can you clarify the requirement in the question? — Jon Armstrong
– Jon Armstrong, Commented Dec 28, 2021 at 14:21
I'm receiving the user id's from [MSTR_MD].[dbo].[v_OpcoGeneral_UserList] and want to replace the text where the retrieved user id exists on the opco_security table. — Ugurcan Kaya
– Ugurcan Kaya, Commented Dec 28, 2021 at 14:23
Basically for instance, if "Ugurcan.Kaya" is received from the view, remove "Ugurcan.Kaya" from the opco_general column on the opco_securty table. — Ugurcan Kaya
– Ugurcan Kaya, Commented Dec 28, 2021 at 14:25
Please update the question with a minimal reproducible test case. But you're using a COUNT to compare against the user id. That seems like a mistake. — Jon Armstrong
– Jon Armstrong, Commented Dec 28, 2021 at 14:26
You would better off showing us what you are actually trying to achieve here. — Thom A
– Thom A ♦, Commented Dec 28, 2021 at 14:52

DonkeyKongII · Accepted Answer · 2021-12-28 15:18:38Z

I think you can do what you're trying to do without the loop using a recursive CTE. I borrowed the idea from this post Multiple replacements in string in single Update Statement in SQL server 2005

I arranged it so that I believe it will work with your use case. The first CTE uses the recursion to continue to apply replacements until no more matches exist.

The second CTE is used to identify the row_number for each recursive replace that occurred. The row_number that we are interested in is the highest row_number per string from the securty table as that will have all the required replacements in it.

Lastly we run an update statement that updates each record in the securty table with the appropriate replaced string.

Here's the code that I wrote to make this work, with example data and tables:

CREATE TABLE UserList(
  id int,
  abbr varchar(25)
);

CREATE TABLE securty(
  id int,
  genrl varchar(250)
);

INSERT INTO UserList(id, abbr)
VALUES(1, 'Ugurcan.Kaya');

INSERT INTO UserList(id, abbr)
VALUES(2, 'User5');

INSERT INTO UserList(id, abbr)
VALUES(3, 'User6');

INSERT INTO securty(id, genrl)
VALUES(1, 'User1-User2-Ugurcan.Kaya-Lorum-User5-User16-User17');

INSERT INTO securty(id, genrl)
VALUES(2, 'User5-Ugurcan.Kaya-Lorum-User5-User16-User17');

;With Replacements as (
    SELECT
        s.id, s.genrl, 0 AS repcount
    FROM
        securty AS s
    UNION ALL
    SELECT
      rs.id, CAST(REPLACE(genrl, ul.abbr, '') AS VARCHAR(250)),rs.repcount+1
    FROM
        Replacements rs
    JOIN
        UserList ul ON CHARINDEX(ul.abbr,rs.genrl) > 0
),
Final_Replacements AS (
  SELECT
    rs.id, rs.genrl, ROW_NUMBER() OVER (PARTITION BY rs.id ORDER BY rs.repcount DESC) AS rn
  FROM
    replacements AS rs
)
UPDATE s
SET s.genrl = fs.genrl
FROM securty AS s
JOIN Final_Replacements AS fs
  ON fs.id = s.id AND fs.rn = 1

I also created a sqlfiddle for you to try it out: http://sqlfiddle.com/#!18/904e0/1/0

Jon Armstrong · Accepted Answer · 2021-12-28 15:28:38Z

2

The fiddle

Maybe this will help understand one approach to applying a set of replacement patterns to your target table in an UPDATE statement.

Just to clarify, REPLACE will replace all the matched occurrences in a string, as seen in the last example string... '6this is XYZ another XYZ test' is updated to '6this is another test'.

From the t-sql documentation:

REPLACE (Transact-SQL)

Replaces all occurrences of a specified string value with another string value.

The SQL:

UPDATE test
   SET test.data = REPLACE(data, pattern, '')
  FROM test
  JOIN abbrev
    ON test.data LIKE CONCAT('%', pattern, '%')
;

The setup:

CREATE TABLE test ( id int, data varchar(40) );

INSERT INTO test VALUES
    ( 1, '1this is a NameToRemove test' )
  , ( 2, '2this is a NameToNotRemove test' )
  , ( 3, '3this is another NameXToRemove test' )
  , ( 4, '4this is another NameXToRemove test' )
  , ( 5, '5this is another NameXToRemove test' )
  , ( 6, '6this is XYZ another XYZ test' )
;

CREATE TABLE abbrev ( id int, pattern varchar(20) );
INSERT INTO abbrev VALUES
    ( 1, 'NameToRemove' )
  , ( 2, 'NameXToRemove' )
  , ( 3, 'NameZToRemove' )
  , ( 4, 'XYZ' )
;

The data:

id	data
1	1this is a NameToRemove test
2	2this is a NameToNotRemove test
3	3this is another NameXToRemove test
4	4this is another NameXToRemove test
5	5this is another NameXToRemove test
6	6this is XYZ another XYZ test

The result:

id	data
1	1this is a test
2	2this is a NameToNotRemove test
3	3this is another test
4	4this is another test
5	5this is another test
6	6this is another test

edited Dec 28, 2021 at 15:28

answered Dec 28, 2021 at 14:53

Jon Armstrong

4,7342 gold badges14 silver badges17 bronze badges

6 Comments

Thom A Over a year ago

This does assume a row can't have more than one abbreviation.

Jon Armstrong Over a year ago

That's right. It's just an example. Other slightly different methods can be used if there are additional requirements not seen in the problem data.

Jon Armstrong Over a year ago

@Larnu Actually, REPLACE does handle replacing all the matched patterns in the string, at least in some cases. I'll update the test case.

Jon Armstrong Over a year ago

But it will have problems with multiple different replacements.

Thom A Over a year ago

db<>fiddle showing what happens with multiple matches.

|

Collectives™ on Stack Overflow

How do I update rows using while loop?

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related