Postgis Update using calculation from various fields

Question

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

Answer 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;

Answer 2

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.