用 PostgreSQL 中另一个表的列更新一个表的列 [英] Update a column of a table with a column of another table in PostgreSQL
问题描述
我想将表 table1
的一列 val1
的所有值复制到另一表 table2 的一列
val2
代码>.我在 PostgreSQL 中试过这个命令:
更新表2设置 val2 = (从 table1 中选择 val1)
但是我收到了这个错误:
<块引用>ERROR: 子查询返回多行用作表达式
有没有其他方法可以做到这一点?
你的UPDATE
查询应该如下所示:
更新 table2 t2设置 val2 = t1.val1从表 1 t1哪里 t2.table2_id = t1.table2_id并且 t2.val2 与 t1.val1 不同;-- 可选,见下文
按照您的方式,两个表的各个行之间没有链接.对于 table2
中的每一行,每一行都将从 table1
中获取.这毫无意义(以昂贵的方式)并且还触发了语法错误,因为这个地方的子查询表达式只允许返回单个值.
我通过加入 table2_id
上的两个表来解决此问题.将其替换为实际链接两者的内容.
我重写了 UPDATE
以加入 table1
(使用 FROM
子句)而不是运行相关子查询,因为这通常更快一个数量级.
它还可以防止 table2.val2
在 table1
中找不到匹配行的情况下无效.相反,使用这种形式的查询,什么都不会发生.
您可以将表表达式添加到 FROM
列表中,就像在普通的 SELECT
中一样(表、子查询、集合返回函数,...).手册:
from_list
表表达式列表,允许来自其他表的列出现在 WHERE
条件和更新表达式中.这是类似于可以在 中指定的表列表FROM
子句SELECT
语句.注意目标表不能出现在from_list
,除非您打算自加入(在这种情况下,它必须from_list
中带有别名).
最后的 WHERE
子句阻止不会改变任何东西的更新 - 这实际上总是一个好主意(几乎全部成本但没有收益,有特殊的例外情况适用).如果新旧值都保证为NOT NULL
,则简化为:
AND t2.val2 <>t1.val1
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
Is there an alternative to do that?
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
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.
I fixed this by joining the two tables on table2_id
. Replace that with whatever actually links the two.
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.
You can add table expressions to the FROM
list like would in a plain SELECT
(tables, subqueries, set-returning functions, ...). The manual:
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 theFROM
Clause of aSELECT
statement. Note that the target table must not appear in thefrom_list
, unless you intend a self-join (in which case it must appear with an alias in thefrom_list
).
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
这篇关于用 PostgreSQL 中另一个表的列更新一个表的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!