从PostgreSQL中的子查询更新或插入(多行和多列) [英] Update or Insert (multiple rows and columns) from subquery in PostgreSQL
问题描述
我正尝试在postgres中执行以下操作:
I'm trying to do something like this in postgres:
-
UPDATE table1 SET( col1,col2)=(从其他表中选择col2,col3 othertable.col1 = 123);
插入表1(col1,col2)值(从其他表中选择col1,col2)
但是,即使使用文档中提到的Postgres 9.0,第1点也不可能( http://www.postgresql.org/docs/9.0/static/sql-update.html )
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)
第二点似乎也不起作用。我收到以下错误:子查询只能返回一列。
Also point 2 seems not working. i'm getting the following error: subquery must return only one column.
希望有人对我有用。否则查询将花费大量时间:(。
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.
推荐答案
对于UPDATE
使用:
For the UPDATE
Use:
UPDATE table1
SET col1 = othertable.col2,
col2 = othertable.col3
FROM othertable
WHERE othertable.col1 = 123;
对于INSERT
使用:
For the INSERT
Use:
INSERT INTO table1 (col1, col2)
SELECT col1, col2
FROM othertable
您不需要 VALUES
语法(如果您使用SELECT填充INSERT值)。
You don't need the VALUES
syntax if you are using a SELECT to populate the INSERT values.
这篇关于从PostgreSQL中的子查询更新或插入(多行和多列)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!