updating table rows in postgres using subquery
Postgres allows:
UPDATE dummySET customer=subquery.customer, address=subquery.address, partn=subquery.partnFROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...) AS subqueryWHERE dummy.address_id=subquery.address_id;This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
You're after the UPDATE FROM syntax.
UPDATE table T1 SET column1 = T2.column1 FROM table T2 INNER JOIN table T3 USING (column2) WHERE T1.column2 = T2.column2;References
- Code sample here: GROUP BY in UPDATE FROM clause
- And here
- Formal Syntax Specification
If there are no performance gains using a join, then I prefer Common Table Expressions (CTEs) for readability:
WITH subquery AS ( SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...)UPDATE dummySET customer = subquery.customer, address = subquery.address, partn = subquery.partnFROM subqueryWHERE dummy.address_id = subquery.address_id;IMHO a bit more modern.