Explore our sites

Stack Exchange
tour help
Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote

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

share|improve this answer
add comment
up vote 0 down vote

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)
share|improve this answer
add comment

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.