Update or Insert (multiple rows and cols) from Subquery in PostgreSQL

Question

hi folks,

i'm trying to do something like this in 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)

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.

Answer 1

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 VALUES syntax if you are using a SELECT to populate the INSERT values.