SQL Server - how to delete recursive rows

Question

I have a department table, I want to select and delete all the departmentid and departmentidparent reference lines from the table for the department identifier, suppose departmentid=13 .

It means

 Departmentid departmentidparent 13 13 14 13 15 13 16 14 17 14 

In this, all rows must be deleted from the table. I am very confused and have no idea how to solve it.

Answer 1

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 a request 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