Update multiple columns/rows case statement SQL

I have looked at similar questions previously but they seem to update a single column with multiple case conditions and the one answer I found for multiple columns seems a little long.

Let’s say I have a table like this:

Column_1	Column_2	Column_3	Column_4	Column_5
First	Value 1
Second	Value 2
….	….
Twelfth	Value 3

I want to use column_1 as a key and update column 3, column 4 and column 5 with known values.

My initial query guess was:

UPDATE table SET
Column_3, Column_4, Column_5
CASE 
When Column_1 = 'First' Then 'first_col_3_val', 'first_col_4_val', 'first_col_5_val'
When Column_1 = 'Second' Then 'second_col_3_val', 'second_col_4_val', 'second_col_5_val'
...
When Column_1 = 'Tenth' Then 'tenth_col_3_val', 'tenth_col_4_val', 'tenth_col_5_val'
END

The solution for a similar question was

UPDATE table 
SET Column_3
CASE
When Column_1 = 'First' Then 'first_col_3_val'
When Column_1 = 'Second' Then 'second_col_3_val'
...
END
SET Column_4
CASE
When Column_1 = 'First' Then 'first_col_4_val'
When Column_1 = 'Second' Then 'second_col_4_val'
...
END
SET Column_5
CASE
When Column_1 = 'First' Then 'first_col_5_val'
When Column_1 = 'Second' Then 'second_col_5_val'
...
END

Would my initial query work? Or am I looking for a different SQL feature like Duplicate keys? I read up on a MySQL If() function but I’m working with Microsoft SQL. Thanks for the help!

Answer

may be you are looking for something like this:

update T
    set T.column_3 = V.column_3
    ,T.column_4 = V.column_4
    ,T.column_5 = V.column_5
from table T
    inner join
    (
        values
        ('first', 'first_3', 'first_4', 'first_5'),
        ('second', 'second_3', 'second_4', 'second_5')
        ...
    ) V (column_1, column_3, column_4, column_5)
    on V.column_1 = T.column_1

Advertisement

Answer