title: Update column within CASE statement with results of a subquery postgres
body: <p>I need to update a column based on the results of a subquery. If the subquery returns results for that column then the columns must be updated, is the query returns no results for that column then I need to update with 0.</p>
  
  <p>I do not know where to place the subquery and how to combine it with the CASE statement. This is what I thought but the syntax is not correct. Can anybody help please?</p>
  
  <pre><code>(SELECT datazones.ogc_fid, count(*) as total 
  FROM suppliersnew suppliers, datazone_report_resupply datazones
  WHERE St_contains(datazones.geom, suppliers.geometry) AND (suppliers.status = 'Under construction' OR
  suppliers.status = 'Unknown' OR suppliers.status = 'Operational') GROUP by datazones.ogc_fid ORDER BY total ASC) sources 
  
  UPDATE datazone_report_resupply
  SET es_actual =
  CASE 
      WHEN datazone_report_resupply.ogc_fid = sources.ogc_fid THEN sources.total
      ELSE 0
  END
  </code></pre>