Update multiple rows in same query using PostgreSQL
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
Based on the solution of @Roman, you can set multiple values:
update users as u set -- postgres FTW
email = u2.email,
first_name = u2.first_name,
last_name = u2.last_name
from (values
(1, '[email protected]', 'Hollis', 'Connell'),
(2, '[email protected]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;