Update a table field by replacing substrings based on values from another table

Question

I have a table and I want to update one of its varchar fields based on the values in an another table.

I have the following table:

ID  Constraint_Value 
----------------------------
1   (OldVal_1) (OldVal_2) 
2   (OldVal_2) (OldVal_1)

... and I want to use the data from the following table to make the update:

oldValue  newValue
----------------------------
OldVal_1    NewVal_1
OldVal_2    NewVal_2

After the update, I am aiming for the following:

ID    Constraint_Value 
----------------------------
1     (NewVal_1) (NewVal_2) 
2     (NewVal_2) (NewVal_1)

The following SQL illustrates my problem (which you can run in SQL Management Studio without any set up) :

IF OBJECT_ID('tempdb..#tmpConstraint') IS NOT NULL DROP TABLE #tmpConstraint
GO 
CREATE TABLE tempdb..#tmpConstraint ( constraint_id INT PRIMARY KEY, constraint_value varchar(256) )
GO

IF OBJECT_ID('tempdb..#tmpUpdates') IS NOT NULL DROP TABLE #tmpUpdates
GO 
CREATE TABLE tempdb..#tmpUpdates ( oldValue varchar(256), newValue varchar(256))
GO

insert into #tmpConstraint
values (1, '(OldVal_1) (OldVal_2)')

insert into #tmpConstraint
values (2, '(OldVal_2) (OldVal_1)')

insert into #tmpUpdates
values ('OldVal_1', 'NewVal_1')

insert into #tmpUpdates
values ('OldVal_2', 'NewVal_2')

select * from #tmpConstraint

update c
set constraint_value = REPLACE(constraint_value, u.oldValue, u.newValue)
from #tmpConstraint c
cross join #tmpUpdates u

select * from #tmpConstraint

This gives the results:

(Before) 
1   (OldVal_1) (OldVal_2) 
2   (OldVal_2) (OldVal_1) 

(After) 
1   (NewVal_1) (OldVal_2) 
2   (OldVal_2) (NewVal_1)

As you can see just OldVal_1 has been updated. OldVal_2 has remained the same.

How do I update the field with all the data in the lookup table?

I realise this question is similar to another question I have asked (stackoverflow.com/questions/10836092/…) but I have removed XML out of the equation on this question so hopefully this may yield some different approaches. Of course, I will update this question with any useful answers from the other question. — Mark Robinson
– Mark Robinson, Commented May 31, 2012 at 15:35
.. Multi-value columns, one of the worst SQL anti-patterns known (for this, and other reasons). Please spindle, fold, and mutilate the original designer. It looks like some versions of SQL Server support CTEs for UPDATE statements - do they support recursive ones in that case? If so, you can probably write a CTE to assemble your new constraint_value ...value. Otherwise, the only thing I can think of would be to run the statement multiple times, so long as a row has an instance of an old value. — Clockwork-Muse
– Clockwork-Muse, Commented May 31, 2012 at 15:43
@X-Zero - As the original designer I will start spindling, folding and mutilating myself as soon as I've finished writing this comment! However, just to give you some context, the actual value in the constraint_value field is a formula (for example "(((100)/OldVal_1)*OldVal_2)" ) that I have to update certain elements of (for reasons too tedious to explain). As you can imagine, a formula doesn't lend itself very well to being stored in a relational database especially as this formula may turn into more of a conditional algorithm in the future. Its still an anti-pattern though! — Mark Robinson
– Mark Robinson, Commented Jun 1, 2012 at 8:39
Sure you can store formulas in a database (assuming you're parsing out the formulas for evaluation application-side). I'd start with a recursive structure of some sort, and nest later operations in deeper levels. There's a few variations of this (and I can't find the diagram I was looking for), but essentially you're storing the evaluation tree. I will admit this (probably) takes more space, though. — Clockwork-Muse
– Clockwork-Muse, Commented Jun 1, 2012 at 16:21

Aaron Bertrand · Accepted Answer · 2012-05-31 15:46:56Z

1

The UPDATE will only affect each source row once. So the easiest workaround I know of is a cursor.

DECLARE @o VARCHAR(256), @n VARCHAR(256);

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY 
FOR SELECT oldValue, newValue FROM #tmpUpdates;

OPEN c;

FETCH c INTO @o, @n;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #tmpConstraint 
      -- note full match only:
      SET constraint_value = REPLACE(constraint_value, '(' + @o + ')', '(' + @n + ')')
      -- and note we only touch rows where there is a full match:
      WHERE constraint_value LIKE '%(' + @o + ')%';

    FETCH c INTO @o, @n;
END

CLOSE c;
DEALLOCATE c;

SELECT constraint_id, constraint_value FROM #tmpConstraint;

Results:

constraint_id  constraint_value
-------------  ---------------------
1              (NewVal_1) (NewVal_2)
2              (NewVal_2) (NewVal_1)

answered May 31, 2012 at 15:46

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GilM Over a year ago

Or, you could skip the cursor, add the WHERE clause to the original Update, and use WHILE @@ROWCOUNT>0 (this would also handle cascading changes, where NewVal_n = OldVal_m and you want to OldVal_n to end up as NewVal_m).

Aaron Bertrand Over a year ago

@GilM yes true, that would work, but I think an explicit cursor is a little more straightforward (even if it is a little more code) and shouldn't perform any differently. Your second thought would require very careful planning to make sure the cascade was processed in the correct order (which again would be easier to guarantee with the cursor).

GilM Over a year ago

With the @@ROWCOUNT solution, the order wouldn't matter (but cycles would be possible, so that would have to be handled). But, I'm not religious about it. Row-By-Row processing is easier to understand, and that's a good thing. I've mostly made my peace with using cursors occasionally (they used to be the source of many maintenance issues in the old days due to sloppy coding and sql engine bugs, so I'm still a bit leery of them). The WHILE @@ROWCOUNT>0 was an effective way to traverse arbitrarily deep hierarchies before CTEs, so I still have a soft spot for that pattern.

Aaron Bertrand Over a year ago

@GilM Order would matter for cascade by definition. With a simple UPDATE SET WHERE, how would you control whether a->b/b->c wasn't processed in the opposite order? I'm not saying you can't do it, but it requires more than just the update statement and the while loop.

GilM Over a year ago

Whenever there is an a, a would be replaced by b. Whenever there is a b, b would be replaced by c. Order doesn't matter. All a's and all b's eventually become c's.

|

Collectives™ on Stack Overflow

Update a table field by replacing substrings based on values from another table

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related