使用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:

我想将表table1的一列val1中的所有值复制到另一个table2的一列val2。我在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

错误：用作表达式的子查询返回的多行

Is there an alternative to do that?

有没有替代方法呢？

2 个解决方案

#1

Your UPDATE query should really look like this:

您的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, to avoid empty updates

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

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

我通过连接table2_id上的两个表来修复此问题。用两者实际链接替换它。
I rewrote the UPDATE to join in table1 (with the FROM clause) instead of running correlated subqueries, because that is regularly 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.

我重写UPDATE以加入table1（使用FROM子句）而不是运行相关子查询，因为这通常会更快一个数量级。它还可以防止table2.val2在table1中找不到匹配的行时无效。相反，使用这种形式的查询，这些行没有任何反应。
You can include all the same things in the FROM list you could include in a plain SELECT (like multiple tables or subqueries). Per documentation:

您可以在FROM列表中包含所有相同的内容，可以包含在普通的SELECT中（如多个表或子查询）。每个文件：

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条件和更新表达式中。这类似于可以在SELECT语句的FROM子句中指定的表的列表。请注意，目标表不得出现在from_list中，除非您打算进行自联接（在这种情况下，它必须在from_list中显示别名）。

The final WHERE clause prevents updates that wouldn't change anything - which is practically always a good idea (full cost, but no gain - exotic exceptions apply).
最后的WHERE子句可以防止不会改变任何内容的更新 - 这实际上总是一个好主意（全部成本，但没有收益 - 异乎寻常的例外适用）。

#2

update table1 set table1_column= table2.column from table2 table2 where table1_id= table2.id

update table1 set table1_column = table2.column from table2 table2 where table1_id = table2.id

do not use alias name for table1.
不要为table1使用别名。
tables are table1, table2
表是table1，table2

#1