CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Michael Bui
Michael Bui

Reputation: 300

Update multiple rows in one query

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?

Upvotes: 0

Views: 448

Answers (1)

jarlh
jarlh

Reputation: 44696

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';

Upvotes: 1

Related Questions