Update or Insert multiple rows and columns from subquery in PostgreSQL

Question

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

User · Accepted Answer

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

User · Answer

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   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  gt   2016-01-01    AS subquery WHERE table1 id   subquery col1

User · Answer

UPDATE table1 SET  col1  col2     col2  col3  FROM othertable WHERE othertable col1   123

[sql] Update or Insert (multiple rows and columns) from subquery in PostgreSQL

For the UPDATE

For the INSERT

Examples related to sql

Examples related to postgresql

Examples related to sql-update