SQLTeam.com | Weblogs | Forums

Updating all values in a column to new values



Hi ,
I am trying to update all the values in a column

SET column_name =
WHEN (label = 'AAA') THEN 'AA1'
WHEN (label = 'CCD') THEN 'C01'
WHEN (label = 'EGE') THEN 'FEF'
ELSE (label)
END ).

Only the labels that are mentioned in the case statement should be updated the rest should remain as it is.
Is CASE statement the only way or is there a better and easy method.

Thanks for the help.


if you have only got 2 or 3 CASEs, or if this is a one-off Update, then CASE statement is probably OK.

If it is a bigger list, or used regularly, particularly if the list of values changes, then I would JOIN to a Lookup Table of replacement values

Something like this:

CREATE TABLE MyReplaceValueTable
    [Key] varchar(123)
    , [ReplaceValue] varchar(123)

SET column_name = ReplaceValue
    JOIN MyReplaceValueTable AS R
        ON R.Key = U.label


I would add a where statement to only update when needed. Something like:
where label in ('AAA','CCD','EGE')


Yes, I would do that too ... but ... there is a risk that that gets out of step with the CASE in the UPDATE and some conditions get missed.

I believe UPDATE doesn't actually process anything that is a non-op change - although SQL must, surely?, have to put the CPU effort into finding all the rows and if there is a TRIGGER then that will fire (for the unchanged rows).

JOINing to a Lookup Table for the change-values achieves the same "filter" of course, but I think its still only worthwhile if the situation is "complex" enough to warrant it


We agree


Thank you Kristen and bitsmed.
I used the join method. It worked.

Thank you