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.
Author |
Topic |
iulianionescu
Starting Member
14 Posts |
Posted - 2010-08-18 : 14:47:54
|
Hi, I have a table defined as follows:dir_CategoriesCategoryID int ParentID intA 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 -1CategoryID 2, Parent 1CategoryID 3, Parent 2CategoryID 4, Parent 3If 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,IulianRegards,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-- resultdir c pA 1 -1E 5 1 |
|
|
|
|
|
|
|