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;
series |
---|
A Game of Thrones -> A Clash of Kings -> A Storm of Swords -> A Feast for Crows -> A Dance with Dragons |
Related Topics
Copy Data from One Existing Row to Another Existing Row in SQL
How to Convert Visual Foxpro Database into SQL Server Database
How to Generate SQL from Dbplyr Without a Database Connection
Writing a Function in SQL to Loop Through a Date Range in a Udf
SQL Datedifference in a Where Clause
Oracle 11G - How to Optimize Slow Parallel Insert Select
Grant Permission to Create Tables - SQL Server
Sql: How to Order Null and Empty Entries to The Front in an Orderby
Get the List of Stored Procedures Created And/Or Modified on a Particular Date
Running Total by Group SQL (Oracle)
Activerecord::Statementinvalid. Pg Error
Is Using Char as a Primary/Foreign Key a No No
Self-Referential Table Fields in MySQL
How to Get Second Highest Salary Department Wise Without Using Analytical Functions
Cannot Connect to Azure SQL Database, Even with Whitelisted Ip