Update or Insert (multiple rows and columns) from subquery in PostgreSQL
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 othertableYou don't need the VALUES syntax if you are using a SELECT to populate the INSERT values.
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 > '2016-01-01') AS subqueryWHERE table1.id = subquery.col1;
UPDATE table1 SET (col1, col2) = (col2, col3) FROM othertable WHERE othertable.col1 = 123;