Recursive delete of table records

Please start any new threads on our new site at https://forums.sqlteam.com. 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

iulianionescu
Starting Member

14 Posts

Posted - 2010-08-18 : 14:47:54

Hi, I have a table defined as follows:

dir_Categories
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!

Thanks,

Iulian

Regards,

Iulian

namman
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources