| Author |
 Topic  |
|
|
iulianionescu
Starting Member
USA
14 Posts |
 Posted - 08/18/2010 : 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
USA
258 Posts |
Posted - 08/18/2010 : 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
|
Edited by - namman on 08/18/2010 23:09:05 |
 |
|
| |
Topic |
|
|
|
Recursive delete of table records
