CODEWITHSUNDEEP
sqlsql-servert-sql
nnmmss
nnmmss

Reputation: 2992

T-SQL recursive query - how to do it?

I have a table with self referencing relation, 

ID  parentID UserId Title 
 1    null     100   A
 2     1       100   B
 3     2       100   C 
 4     2       100   D
 5     null    100   E
 6     5       100   F

I want to update UserId from 100 to 101 for all records with ID=1 and its children, so I want to have

ID  parentID UserId Title 
 1    null     101   A
 2     1       101   B
 3     2       101   C 
 4     2       101   D
 5     null    100   E
 6     5       100   F

How can I do it in T-SQL?

Upvotes: 10

Views: 16935

Answers (1)

podiluska
podiluska

Reputation: 51514

You probably want to use a common table expression which allows you to generate recursive queries.

eg:

;with cte as 
(
    select * from yourtable where id=1
    union all
    select t.* from cte 
        inner join yourtable t on cte.id = t.parentid
)
    update yourtable
    set userid = 101
    where id in (select id from cte)

Upvotes: 25

Related Questions