Deleting hierarchical data in SQL table

sql, sql-server-2005

Solution

When the number of rows is not too large, erikkallen's recursive approach works.

Here's an alternative that uses a temporary table to collect all children:

create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
    insert into #nodes 
    select distinct child.id 
    from table child
    inner join #nodes parent on child.parentid = parent.id
    where child.id not in (select id from #nodes)

delete
from table
where id in (select id from #nodes)

It starts with the row with @delete_id and descends from there. The where statement is to protect from recursion; if you are sure there is none, you can leave it out.

Problem

I have a table with hierarchical data. A column "ParentId" that holds the Id ("ID" - key column) of it's parent. When deleting a row, I want to delete all children (all levels of nesting). How to do it? Thanks

Original source