0

Trying to update one table with a calculation using data from another table. I construct a subquery that relates acreagetot and acreage_wetland fields and use it to perform a calculation on UPDATE THEN clause. Planner says 'Subquery has too many fields' SQL state: 42601.

WITH
type_2 AS (
SELECT v.parcel_id,v.acreagetot,w.acreage_wetland,v.geom
FROM mi.wl_int_sum AS w JOIN mi.bay_mi0 AS v ON w.parcel_id=v.parcel_id WHERE acreage_wetland>0)

UPDATE mi.bay_mi0
SET acreage_dry = (
    CASE
      WHEN parcel_id IN (TABLE type_2)
      THEN acreagetot - acreage_wetland
      ELSE NULL
    END
);

Both geometries uses same srid, 4326

And Postgresql versions are: -PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit -POSTGIS="2.4.0 r15853" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER

1

Found this solution that works:

WITH
type_2 AS (
SELECT v.parcel_id,v.acreagetot,w.acreage_wetland,v.geom
FROM mi.bay_mi0 AS v 
LEFT JOIN mi.wl_int_sum  AS w  
ON w.parcel_id=v.parcel_id 
WHERE w.acreage_wetland>0 )

UPDATE mi.bay_mi0
SET acreage_dry = type_2.acreagetot - type_2.acreage_wetland
FROM type_2
WHERE mi.bay_mi0.parcel_id= type_2.parcel_id;
1

I think you are thinking this too complex.
If I understand your need right you can get same result with

UPDATE bay_mi0 AS v 
SET acreage_dry = v.acreagetot - w.acreage_wetland
FROM wl_int_sum AS w
WHERE v.parcel_id= w.parcel_id and acreage_wetland > 0;

In your WITH subquery you are just joining data that you are doing again in actual UPDATE part. You also select fields as is so there are no use for separate WITH part. Geometry SRIDs doesn't matter since you are not doing any geometry operations. Actually this has very little to do with PostGIS.

Hopefully this gives you some help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.