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.

Milen A. Radev · Accepted Answer · 2010-09-17 16:14:36Z

252

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.

edited Sep 17, 2010 at 16:14

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

answered Sep 17, 2010 at 15:47

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user Over a year ago

Is it possible to combine Update & Insert so that if one fails, the other will be used without throwing an error (independently for each row). I think that would be a more complete solution to the question (for example: stackoverflow.com/a/6527838/781695)

David Namenyi · Accepted Answer · 2016-02-15 19:34:04Z

41

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;

answered Feb 15, 2016 at 19:34

David Namenyi

87611 silver badges9 bronze badges

2 Comments

mythicalcoder Over a year ago

perfect. this works best wherein you need to pick a subset based on a condition involving the updated table itself.

Chintan Pathak Over a year ago

@David Namenyi can you explain this query?

Walter · Accepted Answer · 2011-12-15 21:09:17Z

20

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

edited Dec 15, 2011 at 21:09

user142162

answered Sep 5, 2011 at 14:20

Walter

2012 silver badges2 bronze badges

Comments

SpiderMan · Accepted Answer · 2025-02-17 11:01:28Z

It's always best to run query in transaction first so that you can revert it if result is differ from what you want to achieve, here is what I used for above problem

BEGIN; -- beginning of transaction

UPDATE admin_layer.mh_village vill SET 
(dtncode, dtname, crncode, crname, subdname, thncode) =
(n.dtncode, n.dtname, n.crncode, n.crname, n.subdname, n.thncode)
FROM admin_layer.update_gp_codes n
WHERE n.vincode = vill.vincode AND vill.phase = 2;


-- Check result with select query, because you are in transaction
-- it will show you actual result just like real update

SELECT id, stncode, stname, dvncode, dvname, dtncode, dtname,
 crncode, crname, subdname, thncode
    FROM admin_layer.mh_village WHERE phase = 2 AND gpncode is null

-- ROLLBACK; -- if you want to revert it uncomment
-- COMMIT; -- if result is what you want

Collectives™ on Stack Overflow

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

4 Answers 4

For the UPDATE

For the INSERT

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

For the UPDATE

For the INSERT

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related