title: Update a table recursively in Postgres
body: <p>I am trying to update a table (which has a tree structure) using recursion.  I use the following code (only difference is that the update part is not used and the last select queries from the CTE instead of the original table) to show the sub-tree of any given node, in this case it is hard coded 'G'. </p>
  
  <p>I now need to update the values of the sub tree but using the below code I am having an error which cause cannot be identified. A systax error is given near the update query. </p>
  
  <pre><code>WITH RECURSIVE ph AS
  (
      -- Anchor
      SELECT attr1, attr3, attr4
      FROM entity
      WHERE attr3 = 'G'
  
      UNION ALL
  
      -- Recursive Member
      SELECT entity.attr1, entity.attr3, entity.attr4
      FROM entity, ph     
      WHERE ph.attr1 = entity.attr3
  
      -- update original table (not the cte)
      UPDATE entity
      SET attr4 = entity.attr4 * 1.1
      FROM ph
      WHERE ph.attr1 = entity.attr3;
  )
  
      --see result with updated values
      Select * from entity;
  </code></pre>
  
  <p>Solved with the below query.</p>
  
  <pre><code>WITH RECURSIVE ph AS
  (
      -- Anchor
      SELECT attr1, attr3, attr4
      FROM entity
      WHERE attr3 = 'E'
  
      UNION ALL
  
      -- Recursive Member
      SELECT entity.attr1, entity.attr3, entity.attr4
      FROM entity, ph     
      WHERE ph.attr1 = entity.attr3
  )
  
      --see result with updated values
      UPDATE entity
      SET attr4 = attr4 * 100
      WHERE attr1 IN (SELECT attr1 FROM ph)
      RETURNING *;
  </code></pre>