How to Fix: ERROR: missing FROM-clause entry for table "" in postgres-CodePudding

I have seen many questions about this error already. But seems nothing worked for me. Please share your suggestions. Here is my query, where I am trying to update the new_shp_id column of geonames tables, from geoboundaries table when geom of both tables intersect.

UPDATE 
    public.geonames
SET 
    new_shp_id = subquery.gbID
FROM 
    (
        SELECT gb.shapeid AS gbID
            FROM "public"."geoBoundaries" gb, "public"."geonames" gn
    ) AS subquery
WHERE 
    ST_Intersects(gn.geom, gb.geom);

Error:

ERROR: missing FROM-clause entry for table "gn"
LINE 11: ST_Intersects(gn.geom, gb.geom);

CodePudding user response：

You don't need the derived table. Just put the geoboundaries directly in the FROM clause and give an alias to the target table:

UPDATE public.geonames gn
  SET new_shp_id = gb.shapeid
FROM "public"."geoBoundaries" gb
WHERE ST_Intersects(gn.geom, gb.geom);