SQL Server - Recursive Delete

Sql server - recursive delete

Oracle solution:

How to generate DELETE statements in PL/SQL, based on the tables FK relations?

SQL Server solution:

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

Hope it helps

SQL Server - Cascading DELETE with Recursive Foreign Keys

The below might work for you (I haven't tested it so it may require some tweaking). Seems like all you have to do is delete the employees from the bottom of the hierarchy before you delete the ones higher-up. Use a CTE to build the delete hierarchy recursively and order the CTE output descending by the hierarchy level of the employee. Then delete in order.

CREATE PROC usp_DeleteEmployeeAndSubordinates (@empId INT)
AS

;WITH employeesToDelete AS (
SELECT id, CAST(1 AS INT) AS empLevel
FROM employee
WHERE id = @empId
UNION ALL
SELECT e.id, etd.empLevel + 1
FROM employee e
JOIN employeesToDelete etd ON e.boss_id = etd.id AND e.boss_id != e.id
)
SELECT id, ROW_NUMBER() OVER (ORDER BY empLevel DESC) Ord
INTO #employeesToDelete
FROM employeesToDelete;

DECLARE @current INT = 1, @max INT = @@ROWCOUNT;

WHILE @current <= @max
BEGIN
DELETE employee WHERE id = (SELECT id FROM #employeesToDelete WHERE Ord = @current);
SET @current = @current + 1;
END;
GO

SQL - Recursive delete in one query?

As suggested by @jarlh, a really nice solution is having a Foreign key, with on delete cascade.

Remove rows from recursive query

You'd want to start from the roots of the trees.

There are no roots in the prequelId's.

WITH RECURSIVE series AS (
SELECT
b.bookId
, 1 as lvl
, p.prequelID
, CONCAT(b.title) as series
FROM Books AS b
LEFT JOIN Prequels AS p
ON p.bookId = b.bookId
WHERE NOT EXISTS (
SELECT 1
FROM Prequels p2
WHERE p2.prequelID = b.bookId
)

UNION ALL

SELECT
s.bookId
, s.lvl+1
, p.prequelID
, CONCAT(b.title, ' -> ', s.series)
FROM series AS s
JOIN Books AS b
ON b.bookId = s.prequelID
LEFT JOIN Prequels AS p
ON p.bookId = s.prequelID
)
SELECT series
FROM series
WHERE prequelId is null
AND lvl > 1
ORDER BY series;


Leave a reply



Submit