0 votes
1 view
in SQL by (20.3k points)

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: the subquery must return only one column.

I hope somebody has a workaround for me. otherwise, the queries will take a lot 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.

1 Answer

0 votes
by (40.4k points)

Use the following query for the UPDATE:

UPDATE table1 

   SET col1 = othertable.col2,

       col2 = othertable.col3 

  FROM othertable 

 WHERE othertable.col1 = 123;

Use the below query for the INSERT:

INSERT INTO table1 (col1, col2) 

SELECT col1, col2 

  FROM othertable

Note:  If you are using the SELECT statement to populate the INSERT values then you don't need the VALUES syntax.

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 16, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !