When using a query of this form:
UPDATE a SET columns=values FROM b WHERE ..., the
b subquery is evaluated independently of
a, and it's the WHERE block at the end of the query that is supposed to join the rows of
The query in the question attempts to join inside the subquery rather than in a WHERE clause at the uppermost level (in fact it doesn't have such a WHERE clause), but it's not allowed and that's what the error message indicates.
The SQL-standard way of updating each row with a correlated subquery would be:
UPDATE a SET (col1,col2) =
(SELECT val1,val2 FROM b WHERE expression-needing-values-from-a)
but in older versions of PostgreSQL (9.4 or before), the manual explicitly tells that this form is not implemented. Starting with 9.5, it is implemented.
The typical way to emulate it with postgres 9.4 is to use a copy of table
a in the subquery and join back to the updated table in the upper level's WHERE clause, like this:
UPDATE a SET (col1,col2)=(s.col1,s.col2)
FROM (SELECT a2.ctid,col1,col2 AS ctid_of_row FROM b, a AS a2 WHERE...) AS s
WHERE s.ctid_of_row = a.ctid;
ctid is the system pseudo-column that indicates the physical location of the row version within its table. Alternatively, the primary key can be used if there is one.
In your case, the transformation is not obvious because your subquery is meant to produce only one row with its
ORDER BY... LIMIT 1 at the end. It should be redesigned to produce all the target rows in a single resultset, presumably
gid are determined per