CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Pankaj Gadge
Pankaj Gadge

Reputation: 2814

Update multiple columns for multiple rows in one query of SQL

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

enter image description here

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 enter image description here

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.

Upvotes: 22

Views: 80187

Answers (2)

echo_Me
echo_Me

Reputation: 37233

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)**

Upvotes: 37

camille khalaghi
camille khalaghi

Reputation: 2239

You can also hack the insert operation :

INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c)

Upvotes: 15

Related Questions