Update a table recursively in Postgres

Question

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'.

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.

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;

Solved with the below query.

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

Answer 1

UPDATE can't be part of a recursive SELECT. You need do UPDATE using the result of the SELECT:

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;