By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Test
(
Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[Name] NVARCHAR(50) NOT NULL,
ParentId INT NULL FOREIGN KEY REFERENCES Test(Id)
)
INSERT INTO Test(Name) VALUES ('ABC'),('DEF'),('HIJ');
INSERT INTO TEST(Name,ParentId) VALUES ('KLM',1),('NOP',1),('QRS',2),('TUV',2),('XYX',3)
INSERT INTO Test(Name, ParentId) VALUES('AAB',4),('AAC',4),('AAD',4)
11 rows affected
SELECT *
FROM dbo.Test;
| Id | Name | ParentId |
|---|---|---|
| 1 | ABC | null |
| 2 | DEF | null |
| 3 | HIJ | null |
| 4 | KLM | 1 |
| 5 | NOP | 1 |
| 6 | QRS | 2 |
| 7 | TUV | 2 |
| 8 | XYX | 3 |
| 9 | AAB | 4 |
| 10 | AAC | 4 |
| 11 | AAD | 4 |
DECLARE @ID int = 1;
WITH rCTE AS(
SELECT T.Id
FROM dbo.Test T
WHERE T.Id = @ID
UNION ALL
SELECT T.Id
FROM rCTE r
JOIN dbo.Test T ON r.Id = T.ParentId)
DELETE T
FROM dbo.Test T
JOIN rCTe r ON T.Id = r.Id;
6 rows affected
SELECT *
FROM dbo.Test;
| Id | Name | ParentId |
|---|---|---|
| 2 | DEF | null |
| 3 | HIJ | null |
| 6 | QRS | 2 |
| 7 | TUV | 2 |
| 8 | XYX | 3 |