CODEWITHSUNDEEP
sqlpostgresqlsql-update
dforce
dforce

Reputation: 2234

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

I'm trying to do something like this in postgres:

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.

Upvotes: 154

Views: 210414

Answers (3)

David Namenyi
David Namenyi

Reputation: 846

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 subquery
WHERE table1.id = subquery.col1;

Upvotes: 41

Walter
Walter

Reputation: 191

UPDATE table1 SET (col1, col2) = (col2, col3) FROM othertable WHERE othertable.col1 = 123;

Upvotes: 19

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 248

Related Questions