sql - 在 PostgreSQL 中用另一个表的列更新一个表的列

Question

我想将表 table1 的一列 val1 中的所有值复制到另一个表 table2 的一列 val2 。我在 PostgreSQL 中试过这个命令:

update table2
set val2 = (select val1 from table1)

但是我得到了这个错误:

ERROR:  more than one row returned by a subquery used as an expression

有替代方案吗？

Answer 1

你的 UPDATE查询应如下所示:

UPDATE table2 t2
SET    val2 = t1.val1
FROM   table1 t1
WHERE  t2.table2_id = t1.table2_id
AND    t2.val2 IS DISTINCT FROM t1.val1;  -- optional, see below

按照您的方式，两个表的各个行之间没有链接。对于 table2 中的每一行，将从 table1 中获取每一行。这毫无意义(以一种昂贵的方式)并且还触发了语法错误，因为这个地方的子查询表达式只允许返回一个值。

我通过连接 table2_id 上的两个表来解决这个问题。将其替换为您的实际加入条件。

我重写了 UPDATE 以加入 table1(使用 FROM 子句)而不是运行相关的子查询，因为这通常更快。
它还可以防止 table2.val2 在 table1 中找不到匹配行的情况下被取消。相反，使用这种形式的查询，这些行什么都不会发生。

您可以将表表达式添加到 FROM 列表，就像在普通 SELECT 中一样(表、子查询、集合返回函数，...)。 The manual:

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the samesyntax as the FROM clause of a SELECT statement; for example,an alias for the table name can be specified. Do not repeat the targettable as a from_item unless you intend a self-join (in whichcase it must appear with an alias in the from_item).

最后的 WHERE 子句阻止了不会改变任何东西的更新——几乎付出了全部代价却没有收获(奇异的异常(exception)情况适用)。如果旧值和新值都保证为 NOT NULL，则简化为:

AND   t2.val2 <> t1.val1

参见:

• How do I (or can I) SELECT DISTINCT on multiple columns?