Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I'm looking to update multiple rows in postgres in one statement. Is there a way to do something like the following?

UPDATE table 
SET 
 column_a = 1 where column_b='123',
 column_a = 2 where column_b='345'
share|improve this question
 
I keep trying to find it on that page but I can't get it. I see where you can update multiple rows using one where statement, but I don't get how to update multiple rows each with it's own where statement. I also searched google and didn't find a real clear answer so I was hoping someone could provide a clear example on this. –  newUserNameHere Sep 14 at 2:23
 
Sorry my mistake. Updated. –  zero323 Sep 14 at 2:39

2 Answers

up vote 3 down vote accepted

Yes, you can:

UPDATE foobar SET column_a = CASE
   WHEN column_b = '123' THEN 1
   WHEN column_b = '345' THEN 2
END
WHERE column_b IN ('123','345')

And working proof: http://sqlfiddle.com/#!2/97c7ea/1

share|improve this answer
1  
This is wrong... You will update all rows, even if it is not '123' nor '345'. You should use WHERE column_b IN ('123','456')... –  MatheusOl Sep 14 at 3:54
 
@MatheusOl Corrected, thanks. –  zero323 Sep 14 at 4:03
 
i think '456' supposed to be '345' –  Roman Pekar Sep 14 at 7:39
 
If you add ELSE column_b after the last WHEN ? THEN ? line then the column will be set to it's current value, thus preventing what MatheusQI said would happen. –  Kevin Orriss Oct 22 at 15:43
up vote 6 down vote

You can also use update ... from syntax and use mapping table. If you want to update more than 1 column, it's much more generalizable:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

You can add as much column as you want:

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

share|improve this answer
 
Very nice solution! Thanks. –  newUserNameHere Sep 14 at 13:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.