title: How to write a "recursive update"?
body: <p>I have some kind of a tree stored in a table. It has 2 key columns <code>id</code> and <code>parent_id</code>. And some abstract data for example <code>name</code> and <code>mtime</code>. Lets say this is a file system. 
  <br>I can select all children or all parents from a paticular <code>id</code>. (Like described in <a href="https://stackoverflow.com/questions/6585631/how-can-you-detect-a-parent-with-a-nested-relationship-in-a-database-using-sql">this answer</a>)</p>
  
  <p>The question is how can I update(or delete) such a subtree? <br>For example I want to update the modification time of some node and all of it children (or node and all it's parents upto root). Or delete this node with children. What is the best approach in terms of performance? This table can be really large 100M+ or records.</p>
  
  <p>DDL</p>
  
  <pre><code>create table test (
    id int not null primary key,
    parent_id int not null,
    name varchar(100),
    mtime timestamp default current_timestamp
  );
  insert into test(id, parent_id, name) values(1, 0, "row1");
  insert into test(id, parent_id, name) values(2, 1, "row2");
  insert into test(id, parent_id, name) values(3, 2, "row3");
  insert into test(id, parent_id, name) values(4, 2, "row4");
  insert into test(id, parent_id, name) values(5, 4, "row5");
  insert into test(id, parent_id, name) values(6, 4, "row6");
  insert into test(id, parent_id, name) values(7, 6, "row7");
  </code></pre>
  
  <p>What makes for us this tree:</p>
  
  <pre><code>row1
  |
  row2--row4--row5
  |     |
  row3  row6
        |
        row7
  </code></pre>
  
  <p>--- Update Try1 ---</p>
  
  <p>Tried this as Vladimir suggested:</p>
  
  <pre><code>create procedure upd_test (start_id integer)
  as
  begin
      WITH RECURSIVE
      CTE (id)
      AS
      (
          SELECT T.id
          FROM test AS T
          WHERE T.id = :start_id
  
          UNION ALL
  
          SELECT T.id
          FROM
              test AS T
              INNER JOIN CTE ON CTE.id = T.parent_id
      )
      UPDATE test
          SET mtime = '2001-02-03 10:11:12'
          WHERE id IN (SELECT id FROM CTE);
  end
  </code></pre>
  
  <p>got:</p>
  
  <pre><code>Invalid token.
  Dynamic SQL Error.
  SQL error code = -104.
  Token unknown - line 19, column 5.
  UPDATE.
  </code></pre>