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'
This question is related to
sql
postgresql
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;
In addition to other answers, comments and documentation, the datatype cast can be placed on usage. This allows an easier copypasting:
update test as t set
column_a = c.column_a::number
from (values
('123', 1),
('345', 2)
) as c(column_b, column_a)
where t.column_b = c.column_b::text;
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
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;
Came across similar scenario and the CASE expression was useful to me.
UPDATE reports SET is_default =
case
when report_id = 123 then true
when report_id != 123 then false
end
WHERE account_id = 321;
Reports - is a table here, account_id is same for the report_ids mentioned above. The above query will set 1 record (the one which matches the condition) to true and all the non-matching ones to false.
I don't think the accepted answer is entirely correct. It is order dependent. Here is an example that will not work correctly with an approach from the answer.
create table xxx (
id varchar(64),
is_enabled boolean
);
insert into xxx (id, is_enabled) values ('1',true);
insert into xxx (id, is_enabled) values ('2',true);
insert into xxx (id, is_enabled) values ('3',true);
UPDATE public.xxx AS pns
SET is_enabled = u.is_enabled
FROM (
VALUES
(
'3',
false
,
'1',
true
,
'2',
false
)
) AS u(id, is_enabled)
WHERE u.id = pns.id;
select * from xxx;
So the question still stands, is there a way to do it in an order independent way?
---- after trying a few things this seems to be order independent
UPDATE public.xxx AS pns
SET is_enabled = u.is_enabled
FROM (
SELECT '3' as id, false as is_enabled UNION
SELECT '1' as id, true as is_enabled UNION
SELECT '2' as id, false as is_enabled
) as u
WHERE u.id = pns.id;
For updating multiple rows in a single query, you can try this
UPDATE table_name
SET
column_1 = CASE WHEN any_column = value and any_column = value THEN column_1_value end,
column_2 = CASE WHEN any_column = value and any_column = value THEN column_2_value end,
column_3 = CASE WHEN any_column = value and any_column = value THEN column_3_value end,
.
.
.
column_n = CASE WHEN any_column = value and any_column = value THEN column_n_value end
if you don't need additional condition then remove and
part of this query
Source: Stackoverflow.com