CODEWITHSUNDEEP
sqlsql-server
Phil
Phil

Reputation: 87

Is set based possible instead of cursor loop in this scenario?

Current code:

DECLARE thisLoop CURSOR FOR  
SELECT IdOld, IdNew  
FROM @fieldNewOld;  

OPEN thisLoop;  

FETCH NEXT FROM thisLoop INTO @fIdOld, @fIdNew;  
WHILE @@FETCH_STATUS = 0  
BEGIN

UPDATE dbo.mel
      SET melCode = REPLACE(melCode, '_' + CONVERT(varchar(50),@fIdOld), '_' + CONVERT(varchar(50),@fIdNew))
      where fID = (select IdNew from @newForm) and melCode LIKE ('%_'  + CONVERT(varchar(50), @fIdOld) + '%')

    FETCH NEXT FROM thisLoop INTO @fIdOld, @fIdNew 
END;  

CLOSE thisLoop;  
DEALLOCATE thisLoop;

There is more going on around this, but basically I'm making a copy of a data set. @fieldNewOld contains the old id's and new id's. melCode in dbo.mel contains code written (in a proprietary language) that can reference all of the old id's. It would be referenced something like "myVar_1234" where 1234 would be one of the old id's. When copying, I'm wanting to replace all of the old id's with the new id's in melCode.

I can do it with the cursor loop shown above, but it is VERY slow when there is a large data set. I've also tried the code below, but if there is more than one old id in melCode, it will try to update the same row multiple times and throw an error:

MERGE INTO dbo.mel b
   USING @fieldNewOld a
      ON b.fID = (select IdNew from @newForm) and b.melCode LIKE ('%_' + CONVERT(varchar(50), a.IdOld) + '%')
WHEN MATCHED THEN
   UPDATE 
      SET b.melCode = REPLACE(b.melCode, '_' + CONVERT(varchar(50),a.IdOld), '_' + CONVERT(varchar(50),a.IdNew));

Is there any way to accomplish this in a set based fashion, or am I stuck with the slowness of the cursor loop?

Example Data:

@fieldNewOld

IdOld | IdNew
1234 | 5678
1235 | 5679
1236 | 5680

melCode (all of this would be in one row)

userok("My variable: " + myVar_1234)
myVar_1234 = "this is a test"
newVar_1236 = "here is some more data"

After copying I would want melCode to look like this:

userok("My variable: " + myVar_5678)
myVar_5678 = "this is a test"
newVar_5680 = "here is some more data"

Upvotes: 2

Views: 76

Answers (2)

Parfait
Parfait

Reputation: 107577

Consider an update with JOIN of your final table and table variables:

UPDATE m
SET m.melCode = REPLACE(m.melCode, '_' + CONVERT(varchar(50), f.IdOld), 
                        '_' + CONVERT(varchar(50), f.IdNew))
FROM dbo.mel m
JOIN @fieldNewOld f 
  ON m.melCode LIKE ('%_'  + CONVERT(varchar(50), f.IdOld) + '%')
JOIN @newForm n 
  ON m.fID = n.IdNew

Since you need to update the same text multiple times, consider wrapping above in an iterative update running across the number of rows in fieldNewOld IDs:

DECLARE @Counter INT
DECLARE @ID_Count INT

SET @Counter = 0
SET @ID_Count = (SELECT COUNT(*) FROM @fieldNewOld)

WHILE @Counter <= @ID_Count

BEGIN
    UPDATE m
    SET m.melCode = REPLACE(m.melCode, '_' + CONVERT(varchar(50), f.IdOld), 
                            '_' + CONVERT(varchar(50), f.IdNew))
    FROM dbo.mel m
    JOIN @fieldNewOld f 
      ON m.melCode LIKE ('%_'  + CONVERT(varchar(50), f.IdOld) + '%')
    JOIN @newForm n 
      ON m.fID = n.IdNew 

    SET @Counter += 1
END

Rextester Demo

Upvotes: 1

Luis Cazares
Luis Cazares

Reputation: 3585

You can use JOINs in an UPDATE. 

UPDATE m
SET melCode = REPLACE(melCode, '_' + CONVERT(varchar(50),IdOld), '_' + CONVERT(varchar(50),fIdNew))
FROM dbo.mel m
JOIN @fieldNewOld fno ON m.melCode LIKE ('%_'  + CONVERT(varchar(50), IdOld) + '%')
where fID = (select IdNew from @newForm) ;

This seems like you have a composite value in melCode. My suggestion would be to split the value into 2 or more columns and have melCode as a computed column.

Upvotes: 1

Related Questions