使用PostgreSQL更新同一查询中的多个行。

I'm looking to update multiple rows in PostgreSQL in one statement. Is there a way to do something like the following?

我希望在一个语句中更新PostgreSQL中的多行。是否有一种方法可以做如下的事情?

UPDATE table 
SET 
 column_a = 1 where column_b = '123',
 column_a = 2 where column_b = '345'

5 个解决方案

#1

235

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…从语法和使用映射表。如果你想更新多于一列，它可以更一般化:

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

sql小提琴演示

#2

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

和工作证明:http://sqlfiddle.com/ ! 2/97c7ea / 1

#3

Based on the solution of @Roman, you can set multiple values:

基于@Roman的解决方案，可以设置多个值:

update users as u set -- postgres FTW
  email = u2.email,
  first_name = u2.first_name,
  last_name = u2.last_name
from (values
  (1, 'hollis@weimann.biz', 'Hollis', 'O\'Connell'),
  (2, 'robert@duncan.info', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

#4

Came across similar scenario and the CASE expression was useful to me.

遇到类似的情况时，CASE表达式对我很有用。

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.

这里是一个表，account_id对于上面提到的report_id是相同的。上面的查询将1条记录(匹配条件的记录)设置为true，所有不匹配的记录设置为false。

#5

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 :

假设您有一个id数组和等效的状态数组——这里有一个如何使用数组的静态SQL(由于值不同而不会改变的SQL查询)的示例:

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;

#1

235