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:

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!

CodePudding user response：

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

CodePudding user response：

This is bad design. You're doing something wrong if came to this question.

create table tab(
    Column_1 varchar(100),
    Column_2 varchar(100),
    Column_3 varchar(100),
    Column_4 varchar(100),
    Column_5 varchar(100));

insert tab (Column_1) values ('First'), ('Second'), ('Twelfth');

select top 0 * into #tmp from tab;

insert #tmp (Column_1, Column_3, Column_4, Column_5) values
('First', 'first_col_3_val', 'first_col_4_val', 'first_col_5_val'),
('Second', 'second_col_3_val', 'second_col_4_val', 'second_col_5_val'),
('Twelfth', 'twelfth_col_3_val', 'twelfth_col_4_val', 'twelfth_col_5_val');

update  tab
set     Column_3 = #tmp.Column_3,
        Column_4 = #tmp.Column_4,
        Column_5 = #tmp.Column_5
from    tab join #tmp on tab.Column_1 = #tmp.Column_1;

select * from tab;

db<>fiddle