Updating all values in a column to new values

Transact-SQL
1

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

UPDATE dbo
SET column_name =
( CASE
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.

2

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)
    , PRIMARY KEY
    (
        [Key]
    )
)

UPDATE U
SET column_name = ReplaceValue
FROM MyTable AS U
    JOIN MyReplaceValueTable AS R
        ON R.Key = U.label
3

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

4

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

1 Like
5

We agree

1 Like
6

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

Thank you