Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Recursive delete of table records

Author  Topic 

Starting Member

14 Posts

Posted - 2010-08-18 : 14:47:54
Hi, I have a table defined as follows:

CategoryID int
ParentID int

A category with Parent -1 is a top category, than any category can have a parent.

I want to be able to delete a whole "tree". For example:

CategoryID 1, Parent -1
CategoryID 2, Parent 1
CategoryID 3, Parent 2
CategoryID 4, Parent 3

If I delete the second entry (CategoryID 2), I want both categories 3 and 4 to be deleted as well because they are descendants...

I struggled around this a lot and wasn't able to find a solution other than just grab the information into my C code and do the logic there. But it seems like this should be somehow doable in T-Sql as well, but it's just not clear to me how...

Any help would be appreciated!





Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-18 : 19:08:18
declare @temp table(dir varchar(10), c int, p int)
insert into @temp values('A',1,-1)
insert into @temp values('B',2,1)
insert into @temp values('C',3,2)
insert into @temp values('D',4,3)
insert into @temp values('E',5,1)
insert into @temp values('F',6,2)
insert into @temp values('G',7,3)
select * from @temp

;with t as
select c, p from @temp t2 where c=2 ----category=2
union all select t3.c, t3.p from @temp as t3 inner join t as t4 on t4.c = t3.p
delete @temp where c in (select c from t)

select * from @temp

-- result
dir c p
A 1 -1
E 5 1
Go to Top of Page

- Advertisement -