newUserNameHere newUserNameHere - 1 year ago 71
SQL Question

Update multiple rows in same query using PostgreSQL

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

UPDATE table
column_a = 1 where column_b = '123',
column_a = 2 where column_b = '345'

Answer Source

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