SQL 用PostgreSQL中另一个表的一列更新表的一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13473499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update a column of a table with a column of another table in PostgreSQL
提问by f.ashouri
I want to copy all the values from one column val1of a table table1to one column val2of another table table2. I tried this command in PostgreSQL:
我想将所有值从一个val1表table1的一列复制到val2另一个表的一列table2。我在 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
ERROR: more than one row returned by a subquery used as an expression
Is there an alternative to do that?
有没有其他方法可以做到这一点？
回答by Erwin Brandstetter
Your UPDATEquery should 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, see below
The way you had it, there was no link between individual rows of the two tables. Every row would be fetched from table1for 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.
按照您的方式，两个表的各行之间没有链接。将从table1中的每一行获取每一行table2。这没有任何意义（以一种昂贵的方式）并且还触发了语法错误，因为这里的子查询表达式只允许返回单个值。
I fixed this by joining the two tables on table2_id. Replace that with whatever actually links the two.
我通过加入两个表来解决这个问题table2_id。将其替换为实际将两者联系起来的任何内容。
I rewrote the UPDATEto join in table1(with the FROMclause) instead of running correlated subqueries, because that is typically faster by an order of magnitude.
It also prevents that table2.val2would be nullified where no matching row is found in table1. Instead, nothinghappens to such rows with this form of the query.
我重写了UPDATE加入table1（使用FROM子句）而不是运行相关子查询，因为这通常快一个数量级。
它还可以防止table2.val2在table1. 相反，这种形式的查询不会对此类行产生任何影响。
You can add table expressions to the FROMlist like would in a plain SELECT(tables, subqueries, set-returning functions, ...). The manual:
您可以将表表达式添加到FROM列表中，就像在普通SELECT（表、子查询、集合返回函数等）中一样。手册：
from_listA list of table expressions, allowing columns from other tables to appear in the
WHEREcondition and the update expressions. This is similar to the list of tables that can be specified in theFROMClauseof aSELECTstatement. 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).
from_list表表达式列表，允许其他表中的列出现在
WHERE条件和更新表达式中。这类似于可以在语句的FROM子句中指定的表列表SELECT。请注意，目标表不得出现在 中from_list，除非您打算进行自联接（在这种情况下，它必须以别名出现在 中from_list）。
The final WHEREclause 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:
最后一个WHERE条款防止更新不会改变任何东西 - 这实际上总是一个好主意（几乎全部成本但没有收益，奇异的例外适用）。如果旧值和新值都保证为NOT NULL，则简化为：
AND t2.val2 <> t1.val1
回答by Bharath Pateru
update table1 set table1_column= table2.column from table2 table2 where table1_id= table2.id
更新 table1 set table1_column= table2.column from table2 table2 where table1_id= table2.id
- do not use alias name for table1.
- tables are table1, table2
- 不要为 table1 使用别名。
- 表是表1，表2
