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.

CodePudding user response：

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

CodePudding user response：

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:

The result: