Please help me turn this into an update query

Question

with T1 as 
            (  select tree.* from tree where parent_id = 2
            union all 
            select tree.* from tree 
            join T1 on (tree.parent_id=T1.id) 
            ) 
            select * from T1

This query selects all children nodes in a hierarchical tree.

What I need to do is, with all the results returned from the query above, is update a field called [level] by an increment of 1.

I have tried myself with a few permutations but I get errors about not being able to update a derived table

http://stackoverflow.com/questions/4324720/tsql-a-recursive-update — Tim Schmelter, Jul 27 '13 at 09:24

score 2 · Accepted Answer · answered Jul 27 '13 at 09:24

2

; with  T1 as 
        (
        select  tree.* 
        from    tree 
        where   parent_id = 2
        union all 
        select  tree.* 
        from    tree 
        join    T1 
        on      tree.parent_id=T1.id
        ) 
 update tree
 set    level = level + 1
 where  id in
        (
        select  id
        from    t1
        )

answered Jul 27 '13 at 09:24

Andomar

232,371
49
380
404

`where id in` is not neccessary. use `where id = T1.id` – Royi Namir Jul 27 '13 at 09:25

Please help me turn this into an update query

1 Answers1