用PostgreSQL中另一个表的列更新表的列 [英] Update a column of a table with a column of another table in PostgreSQL

问题描述

我想将表 table1 的一列 val1 的所有值复制到一列<$ c另一个表 table2 的$ c> val2 。我在PostgreSQL中尝试了以下命令：

I want to copy all the values from one column val1 of a table table1 to one column val2 of another table table2. I tried this command in PostgreSQL:

update table2
set val2 = (select val1 from table1)

但是我遇到了这个错误：

But I got this error:

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

有替代方法吗？

推荐答案

您的 UPDATE 查询应类似于

Your UPDATE query should look like this:

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 中提取每一行。这是没有意义的（以昂贵的方式），并且还触发了语法错误，因为在此位置的子查询表达式仅允许返回单个值。

The way you had it, there was no link between individual rows of the two tables. Every row would be fetched from table1 for every row in table2. This made no sense (in an expensive way) and also triggered the syntax error, because a subquery expression in this place is only allowed to return a single value.

我已修复通过连接 table2_id 上的两个表来实现。

I fixed this by joining the two tables on table2_id. Replace that with whatever actually links the two.

我重写了 UPDATE 加入 table1 （带有 FROM 子句），而不是运行相关子查询，因为它通常快一个数量级。

它还可以防止在 table1 中找不到匹配行的情况下将 table2.val2 无效。取而代之的是，在这种形式的查询中，此类行不会出现 。

I rewrote the UPDATE to join in table1 (with the FROM clause) instead of running correlated subqueries, because that is typically faster by an order of magnitude.
It also prevents that table2.val2 would be nullified where no matching row is found in table1. Instead, nothing happens to such rows with this form of the query.

您可以将表表达式添加到 FROM 列表就像在普通的 SELECT 中一样（表，子查询，集合返回函数，...）。 手册：

You can add table expressions to the FROM list like would in a plain SELECT (tables, subqueries, set-returning functions, ...). The manual:

from_list

一个列表表表达式，允许其他表中的列到
出现在 WHERE 条件和更新表达式中。这是
，类似于可以在 FROM 子句
的 SELECT 语句。请注意，除非您打算进行自我联接，否则目标表不得出现在 from_list 的
中（在这种情况下，
必须在 from_list 中带有别名）。

from_list

A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

最后的 WHERE 子句阻止不会更改任何内容的更新-实际上，这始终是好主意（几乎是全额费用，但没有收益，适用例外）。如果新旧值都保证为不为空，则简化为：

The final WHERE clause prevents updates that wouldn't change anything - which is practically always a good idea (almost full cost but no gain, exotic exceptions apply). If both old and new value are guaranteed to be NOT NULL, simplify to:

AND   t2.val2 <> t1.val1

• 如何（或可以）在多列上选择DISTINCT？


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

这篇关于用PostgreSQL中另一个表的列更新表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！