使用PostgreSQL批量更新同一查询中的多行

Question

我想在一条语句中更新PostgreSQL和Go中的多行。有没有办法做如下事情？

UPDATE table 
SET column_a = "FINISH", 
    column_b = 1234 
WHERE id = '1',
    column_a = "UNFINISH", 
    column_b = 3124 
WHERE id = '2' 

还有有没有一个例子，如果用go语言执行？

Answer 1

我很喜欢使用派生表来构建它：

UPDATE t
    SET column_a = v.column_a,
        column_b = v.column_b
    FROM (VALUES (1, 'FINISH', 1234),
                 (2, 'UNFINISH', 3124)
         ) v(id, column_a, column_b)
    WHERE v.id = t.id;

这使得添加额外的值(包括< code>id和列)变得简单，而不会使查询变得复杂。它也不容易出现意外错误。

Answer 2

我想你想要:

update mytable
set 
    column_a = case when id = 1 then 'FINISH' else 'UNFINISHED' end,
    column_b = case when id = 1 then 1234     else 3124 end
where id in (1, 2)

理由：

>

where 子句仅对要更新的 id进行筛选，这样效率更高（前提是您在 id 上有索引），并简化了条件逻辑

id看起来像一个数字，所以应该这样对待（即，不要用引号包围文字值）

文字字符串必须用单引号包围-双引号代表标识符（例如列名）

Answer 3

在Postgres中执行此操作的最有效方法是使用UNNEST。这允许您每列传递一个参数（而不是行数x列数）。对于您的示例，如下所示：

UPDATE table
SET
  column_a=bulk_query.updated_column_a,
  column_b=bulk_query.updated_column_b
FROM
  (
    SELECT * FROM UNNEST(
      ?::TEXT[],
      ?::TEXT[],
      ?::INT[]
    ) AS t(id, updated_column_a, updated_column_b)
  ) AS bulk_query
WHERE
  users.id=bulk_query.id

然后可以传递三个参数：

[
  ["1", "2"],
  ["FINISH", "UNFINISH"],
  [1234, 3124]
]

这种方法的优点是，无论要更新多少行，您都只需要这3个参数。

我在这篇博文中写了更多关于这个主题的内容：https://www.atdatabases.org/blog/2022/01/21/optimizing-postgres-using-unnest#update-multiple-records-to-different-values-in-a-single-query