SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Recursive delete of table records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

14 Posts

Posted - 08/18/2010 :  14:47:54  Show Profile  Visit iulianionescu's Homepage  Reply with Quote
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

284 Posts

Posted - 08/18/2010 :  19:08:18  Show Profile  Reply with Quote
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

Edited by - namman on 08/18/2010 23:09:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000