CODEWITHSUNDEEP
sql-server-2008
user2624315
user2624315

Reputation: 327

Delete all recursive children IDs with parent ID

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

I want a query that recursively delete the data which matches the employeeID with managerID.

For an example when i delete the employeeID with 1 values, it should delete the managerID of 1 and again recursively check the managerID 1 = 273 EmployeeID and compare that 273 ManagerID and find out employeeID of 16 and so on.

Assume we are deleting recursively the child id compare to parentid recursively like in shopping cart, when we delete the parent category, it deletes all the products and its child product recursively.

Upvotes: 3

Views: 4541

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166486

You could try something like

DECLARE @EmployeeID INT = 1
;WITH Vals AS (
  SELECT *
  FROM Table1
  WHERE EmployeeID = @EmployeeID
  UNION ALL
  SELECT t1.*
  FROM Table1 t1 INNER JOIN
  Vals v ON t1.ManagerID = v.EmployeeID
 )
DELETE
FROM Table1
WHERE EmployeeID IN (SELECT EmployeeID FROM Vals)

SQL Fiddle DEMO

Upvotes: 7

Related Questions