Update multiple rows in same query using PostgreSQL

Question

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' 

Answer 1

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