SQL Server - how to delete recursive rows

sql, sql-server-2008

Solution

With a sample table

create table tbl (departmentid int, departmentidparent int)
insert tbl select 13,13
insert tbl select 14,13
insert tbl select 15,13
insert tbl select 16,14
insert tbl select 17,11
insert tbl select 115,17

This is the query that will do what you need

;with cte as
(
select *
 from tbl
 where departmentid=13
 union all
 select tbl.*
 from tbl
 join cte on tbl.departmentidparent=cte.departmentid
 -- the next line is only required because the sample data has parent=self!
 where tbl.departmentid!=cte.departmentid
)
delete tbl
from cte
where tbl.departmentid = cte.departmentid

Problem

I have a `department` table, I want to select and delete all the reference rows of `departmentid` and `departmentidparent` from the table for a department id, let's suppose `departmentid=13`. That means ``` Departmentid departmentidparent 13 13 14 13 15 13 16 14 17 14 ``` In this all the rows should be deleted from the table. I am very confused and don't have any Idea how to solve it.

Original source