Updating multiple rows with different primary key in one query in PostgreSQL?

Question

I have to update many columns in many rows in PostgreSQL 9.1. I'm currently doing it with many different UPDATE queries, each one that works on a different row (based on the primary key):

UPDATE mytable SET column_a = 12, column_b = 6 WHERE id = 1;
UPDATE mytable SET column_a = 1, column_b = 45 WHERE id = 2;
UPDATE mytable SET column_a = 56, column_b = 3 WHERE id = 3;

I have to do several thousands of these queries.

Is there anyway I can "bulk update" lots of rows in one query in PostgreSQL? If you're using INSERT, you can insert multiple rows at once: (INSERT INTO mytable (column_a, column_b) VALUES ( (12, 6), (1, 45) );), Is there something like that for UPDATE?

Something like:

UPDATE mytable SET (id, column_a, column_b) FROM VALUES ( (1, 12, 6), (2, 1, 45), (3, 56, 3), … )

??

The important points is that each 'VALUE' will only update one row (based on the WHERE id =). Each row will have the same, fixed number of columns that need updating, but each row will have different values for each column, so UPDATE mytable SET column_a = 12, column_b = 6 WHERE id IN (1, 2, 3); won't work.

Immaterial

Answer 1

Yes, you can (and usually it's preferred in SQL) to update several rows at once. There're a few ways to do this, but most readable and elegant I think is to use derived table with id's and values:

update mytable as m set
    column_a = c.column_a,
    column_b = c.column_b
from (values
    (1, 12, 6),
    (2, 1, 45),
    (3, 56, 3)
) as c(id, column_a, column_b)
where c.id = m.id

Not so readable, but more obvious solution would be to use case:

update mytable set
    column_a = case id when 1 then 12 when 2 then 1 when 3 then 56 end,
    column_b = case id when 1 then 6 when 2 then 45 when 3 then 3 end
where id in (1, 2, 3)

Santa

This approach worked out great for me!! Thanks!! Also works with joining on multiple key/columns. That is, "where c.id = m.id AND c.column_a=m.column_a"; – Gymnosophist

I get a 'duplicate key value violates unique constraint'. I have a compound unique index and I'm trying to update multiple rows at once to resort them. – Ginglymus

Answer 2

If this applicable to your case you can use it.

create table test(id int, a int, b int);

insert into test(id, a, b)
values
(1, 1, 1),
(2, 1, 1),
(3, 1, 1),
(4, 1, 1),
(5, 1, 1),
(6, 1, 1),
(7, 1, 1);


update test as d
set a = s.a, b = s.b
from 
(
  values
  (1, 2, 2),
  (2, 2, 2)
) as s(id, a, b)
where d.id = s.id

SQL FIDDLE DEMO

Nadeen