Update multiple rows in one query

Question

My current table:

id | count | group_id
1    1      employee   
2    2      employee   
3    3      employee   
4    4      employee

What I want:

id | count | group_id
1    4      employee   
2    3      employee   
3    2      employee   
4    1      employee

What i've attempted

 UPDATE table SET count = 4 WHERE count = 1 AND group_id='employee';
 UPDATE table SET count = 3 WHERE count = 2 AND group_id='employee';
 UPDATE table SET count = 2 WHERE count = 3 AND group_id='employee';
 UPDATE table SET count = 1 WHERE count = 4 AND group_id='employee';

For obvious reason this does not work because it executes each query row by row, so my result is wrong. I think i'm looking for a way of updating multiple tables with one query?

It's actually a column named ordering. And they want to reverse that ordering. — Michael Bui
– Michael Bui, Commented Jan 15, 2016 at 10:36

jarlh · Accepted Answer · 2016-01-15 10:40:46Z

1

This specific case can be solved like this:

UPDATE table SET count = 5 - count
WHERE count between 1 and 4 AND group_id= 'employee';

A more general solution, use a CASE expression:

UPDATE table SET count = case count when 4 then 1
                                    when 3 then 2
                                    when 2 then 3
                                    when 1 then 4
                         end
WHERE count between 1 and 4 AND group_id = 'employee';

edited Jan 15, 2016 at 10:40

answered Jan 15, 2016 at 10:32

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Bui Over a year ago

my bad, it's supposed to be 'employee'

Collectives™ on Stack Overflow

Update multiple rows in one query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related