current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 15 down vote favorite
8

I'm looking to update multiple rows in PostgreSQL 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 '13 at 2:23
    
Sorry my mistake. Updated. –  zero323 Sep 14 '13 at 2:39

2 Answers 2

up vote 29 down vote accepted

You can also use update ... from syntax and use a mapping table. If you want to update more than one 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 many columns as you like:

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 '13 at 13:49
up vote 3 down vote

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 '13 at 3:54
    
@MatheusOl Corrected, thanks. –  zero323 Sep 14 '13 at 4:03
    
i think '456' supposed to be '345' –  Roman Pekar Sep 14 '13 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 '13 at 15:43

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.