Updating table setting a CASE with ST_Intersection

Question

I add a new geometry column to the "mad" table so I could update it with the polygons from an Intersection with the polygons of the table "clip".

Since my database is huge an it was taking a lot of time to comput I opt for this solution "Getting intersections the faster way" (http://postgis.net/2014/03/14/tip_intersection_faster/). These SELECT worked for me but I wanted to do an UPDATE. I tried using this command:

UPDATE mad SET geom_p=CASE WHEN ST_CoveredBy(mad.geom,clip.geom)
    THEN mad.geom 
    ELSE ST_Multi(ST_Intersection(mad.geom,clip.geom)) END AS geom FROM mad AS mad 
  INNER JOIN clip AS clip 
     ON (ST_Intersects(mad.geom,clip.geom) AND NOT ST_Touches(mad.geom,clip.geom)));

ERROR:  syntax error at or near "AS"
LINE 1: ...(ST_Intersection(mad.geom,b.geom)) END AS geom FR...

Answer 1

You get a syntax error, try :

  UPDATE minimad SET geom_p=CASE WHEN ST_CoveredBy(minimad.geom,b.geom)
        THEN minimad.geom 
        ELSE ST_Multi(ST_Intersection(minimad.geom,b.geom)) 
        END
  FROM minimad, min b
  WHERE ST_Intersects(minimad.geom,b.geom) AND NOT ST_Touches(minimad.geom,b.geom);

Also you can use the inner join:

  UPDATE minimad SET geom_p=CASE WHEN ST_CoveredBy(minimad.geom,b.geom)
        THEN minimad.geom 
        ELSE ST_Multi(ST_Intersection(minimad.geom,b.geom)) 
        END
  FROM minimad
  INNER JOIN min as b ON ST_Intersects(minimad.geom,b.geom) AND NOT ST_Touches(minimad.geom,b.geom);

Answer 2

I could not make the UPDATE work so I created a new table

CREATE TABLE madclip AS SELECT mad.gid,mad.cat,
  CASE WHEN ST_CoveredBY(mad.geom,clip.geom) 
     THEN (ST_DUMP(geom)).geom::geometry (Polygon, 904483)
     ELSE (ST_DUMP(ST_Intersection(mad.geom,clip.geom))).geom::geometry   
      (Polygon, 904483) END AS geom 
  FROM mad AS mad INNER JOIN clip as clip 
      ON (ST_Intersects(mad.geom,clip.geom) 
      AND NOT ST_Touches(mad.geom,clip.geom));