SQL 从 PostgreSQL 中的子查询更新或插入（多行和多列）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3736732/
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 or Insert (multiple rows and columns) from subquery in PostgreSQL
提问by dforce
I'm trying to do something like this in postgres:
我正在尝试在 postgres 中做这样的事情：
UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123);INSERT INTO table1 (col1, col2) VALUES (SELECT col1, col2 FROM othertable)
UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123);INSERT INTO table1 (col1, col2) VALUES (SELECT col1, col2 FROM othertable)
But point 1 is not possible even with postgres 9.0 as mentioned in the docs (http://www.postgresql.org/docs/9.0/static/sql-update.html)
但是，即使使用文档中提到的 postgres 9.0，第 1 点也是不可能的（http://www.postgresql.org/docs/9.0/static/sql-update.html）
Also point 2 seems not working. i'm getting the following error: subquery must return only one column.
另外第 2 点似乎不起作用。我收到以下错误：子查询必须只返回一列。
Hope somebody has a workaround for me. otherwise the queries will take a looot of time :(.
希望有人对我有解决方法。否则查询将花费大量时间:(。
FYI: I'm trying to select different columns from several tables and store them into a temporary table, so that another application can easily fetch the prepared data.
仅供参考：我试图从几个表中选择不同的列并将它们存储到一个临时表中，以便另一个应用程序可以轻松获取准备好的数据。
回答by OMG Ponies
For the UPDATE
对于更新
Use:
用：
UPDATE table1
SET col1 = othertable.col2,
col2 = othertable.col3
FROM othertable
WHERE othertable.col1 = 123;
For the INSERT
对于插入
Use:
用：
INSERT INTO table1 (col1, col2)
SELECT col1, col2
FROM othertable
You don't need the VALUESsyntax if you are using a SELECT to populate the INSERT values.
VALUES如果您使用 SELECT 来填充 INSERT 值，则不需要该语法。
回答by David Namenyi
OMG Ponies's answer works perfectly, but just in case you need something more complex, here is an example of a slightly more advanced update query:
OMG Ponies 的回答完美无缺，但以防万一你需要更复杂的东西，这里有一个稍微高级的更新查询的例子：
UPDATE table1
SET col1 = subquery.col2,
col2 = subquery.col3
FROM (
SELECT t2.foo as col1, t3.bar as col2, t3.foobar as col3
FROM table2 t2 INNER JOIN table3 t3 ON t2.id = t3.t2_id
WHERE t2.created_at > '2016-01-01'
) AS subquery
WHERE table1.id = subquery.col1;
回答by Walter
UPDATE table1 SET (col1, col2) = (col2, col3) FROM othertable WHERE othertable.col1 = 123;
