Update multiple columns for multiple rows in one query of SQL

Question

I am trying to set multiple columns for multiple rows in one query, but so far no luck.

Here's how my table looks like

Table: user

I would like to set 'ext_id' on user_id IN (3,4,5) and also like to set ext_flag = Y and admin_role = admin on the same rows.

the resulting table looks like follows

My query looks like this, but I am getting erros due to unfamiliarity to SQL syntax.

update user
set ext_flag = 'Y', admin_role = 'admin', ext_id = 
case 
when user_id = 2 then 345
when user_id = 4 then 456
when user_id = 5 then 789
end

I am having hard time in SET syntax with multiple columns.

Answer 1

try this

 update user
 set ext_flag = 'Y', admin_role = 'admin', ext_id = 
 case 
 when user_id = 2 then 345
 when user_id = 4 then 456
 when user_id = 5 then 789
 end
 **WHERE user_id  in (2,4,5)**