在PostgreSQL中，如何使用一个查询更新具有不同主键的多行数据？

Question

我必须在PostgreSQL 9.1中更新许多行中的许多列。目前，我正在使用许多不同的UPDATE查询来执行此操作，每个查询都作用于不同的行（基于主键）：

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;

我必须执行数千个这些查询。

在PostgreSQL中是否有一种方法可以使用一个查询“批量更新”大量行？如果您正在使用INSERT，则可以一次性插入多行：（INSERT INTO mytable (column_a, column_b) VALUES ((12, 6), (1, 45));），是否有类似UPDATE的东西？

类似于：

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

重点是每个“VALUE”只会更新一行（基于 WHERE id = ）。 每行将具有相同的固定列数需要更新，但每行将具有每个列的不同值，因此UPDATE mytable SET column_a = 12, column_b = 6 WHERE id IN (1, 2, 3); 不起作用。

- Amandasaurus

Answer 1

是的，你可以同时更新多行（通常在SQL中这样做更好）。有几种方法可以实现，但我认为最易读且最优雅的方式是使用带有ID和值的派生表：

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

不太易读，但更明显的解决方案是使用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)

- Roman Pekar

---

这种方法对我非常有效！！谢谢！！同时也适用于在多个键/列上进行连接。也就是说，“where c.id = m.id AND c.column_a=m.column_a”。 - Michael Schreiber

我收到了一个“重复键值违反唯一约束”的错误。我有一个复合唯一索引，并且我正在尝试一次性更新多行以重新排序它们。 - Leeish

Answer 2

如果这对您适用，您可以使用它。

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

- Hamlet Hakobyan