Postgres select and update in same query

Question

I want to do a select and update in a single query in PostgreSQL 9.4. I used below command for that

UPDATE (SELECT t2.pid, t1.pid as newid
 FROM table1 AS t1
 INNER JOIN table2 AS t2 
 ON t1.uid = t2.uid
 ) AS t
SET t.newid = t.pid';

When executing the above, I'm getting below error.

ERROR:  syntax error at or near "("
LINE 2:                 (SELECT t2.pid, t1.pid as n...
                        ^
SQL state: 42601
Character: 25

I executed

SELECT t2.pid, t1.pid as newid
 FROM table1 AS t1
 INNER JOIN table2 AS t2 
 ON t1.uid = t2.uid

part separately and I worked fine. Do I miss something in the update query?

user330315 · Accepted Answer · 2020-12-11 06:40:50Z

1

As documented in the manual the target in an UPDATE can not be a SELECT statement.

But you can use a FROM clause to join another table to the actual target table:

UPDATE t1
   set pid = t2.pid
FROM table2 as t2
WHERE t2.uid = t1.uid;

answered Dec 11, 2020 at 6:40

user330315

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

1 Answer 1