Add the computed percentage of area of polygon into the table in postgresql

Question

I am trying to find the percentage of the polygon inside the other polygon and wanted to write it into the existing table. I have calculated the area but I cannot add it to the existing table.

SELECT 
  ST_Intersection(ST_MakeValid(r.geom),o.buffer) AS intersection,   
  ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100 
FROM public."Residential" r, public."Operation" o 
WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom)); 

I have tried this to add it to the existing table,

ALTER TABLE public."Operation" ADD COLUMN res_percent double precision;
UPDATE public."Operation"  
SELECT      
  ST_Intersection(ST_MakeValid(r.geom),o.buffer) AS intersection,   
  ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100)) 
FROM public."Residential" r, public."Operation" o 
WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom));

I am getting an error

ERROR: syntax error at or near "SELECT"

I just want to add the percentage of the residential polygon (geom) inside the buffer region into a new column in the Operation table.

I have added the fiddle below for the model of the question,

dbfiddle

What I am doing wrong here, How this can be rectified?

Answer 1

Unfortunately we cannot combine select and update statements like that. You rather have to join both tables in the update query to calculate the area, e.g.

UPDATE public."Operation" o
SET res_percent = 
   ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100
FROM public."Residential" r
WHERE ST_Intersects(o.buffer, ST_MakeValid(r.geom));