current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 52 down vote favorite
19

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'
share|improve this question
    
I keep trying to find it on that page but I can't get it. I see where you can update multiple rows using one where statement, but I don't get how to update multiple rows each with it's own where statement. I also searched google and didn't find a real clear answer so I was hoping someone could provide a clear example on this. – newUserNameHere Sep 14 '13 at 2:23
    
Sorry my mistake. Updated. – zero323 Sep 14 '13 at 2:39
up vote 105 down vote accepted
+50

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

share|improve this answer
2  
Very nice solution! Thanks. – newUserNameHere Sep 14 '13 at 13:49
1  
Also, one may have to specify a correct data type. An example with a date: ... from (values ('2014-07-21'::timestamp, 1), ('2014-07-20', 2), ... Further details at the PostgreSQL Documentation – José Andias Dec 30 '14 at 20:16
    
Works great, thank you for clarifying! The Postgres documentation for this makes for a bit of a confusing read. – skwidbreth May 13 at 20:37
up vote 10 down vote

Yes, you can:

UPDATE foobar SET column_a = CASE
   WHEN column_b = '123' THEN 1
   WHEN column_b = '345' THEN 2
END
WHERE column_b IN ('123','345')

And working proof: http://sqlfiddle.com/#!2/97c7ea/1

share|improve this answer
3  
This is wrong... You will update all rows, even if it is not '123' nor '345'. You should use WHERE column_b IN ('123','456')... – MatheusOl Sep 14 '13 at 3:54
    
@MatheusOl Corrected, thanks. – zero323 Sep 14 '13 at 4:03
1  
i think '456' supposed to be '345' – Roman Pekar Sep 14 '13 at 7:39
1  
If you add ELSE column_b after the last WHEN ? THEN ? line then the column will be set to it's current value, thus preventing what MatheusQI said would happen. – Kevin Orriss Oct 22 '13 at 15:43
up vote 3 down vote

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', 'O\'Connell'),
  (2, '[email protected]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;
share|improve this answer
up vote 0 down vote

Let's say you have an array of IDs and equivalent array of statuses - here is an example how to do this with a static SQL (a sql query that doesn't change due to different values) of the arrays :

drop table if exists results_dummy;
create table results_dummy (id int, status text, created_at timestamp default now(), updated_at timestamp default now());
-- populate table with dummy rows
insert into results_dummy
(id, status)
select unnest(array[1,2,3,4,5]::int[]) as id, unnest(array['a','b','c','d','e']::text[]) as status;

select * from results_dummy;

-- THE update of multiple rows with/by different values
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(array[1,2,5]::int[]) as id,unnest(array['a`','b`','e`']::text[]) as status) as new
where rd.id=new.id;

select * from results_dummy;

-- in code using **IDs** as first bind variable and **statuses** as the second bind variable:
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(:1::int[]) as id,unnest(:2::text[]) as status) as new
where rd.id=new.id;
share|improve this answer

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.