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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

iulianionescu
Starting Member

USA
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:

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

USA
285 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  
 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.07 seconds. Powered By: Snitz Forums 2000