CODEWITHSUNDEEP
sqlsql-server
3xGuy
3xGuy

Reputation: 2559

Delete All Children on table that REFERENCES itself

Let's say we have a table that looks like this:

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)
)

In this table, we have a hierarchy of data that may look something like:

INSERT INTO Test (Name) 
VALUES ('ABC'), ('DEF'), ('HIJ');
GO

INSERT INTO TEST (Name, ParentId) 
VALUES ('KLM', 1), ('NOP', 1), ('QRS', 2), ('TUV', 2), ('XYX', 3)
GO

INSERT INTO Test (Name, ParentId) 
VALUES ('AAB', 4), ('AAC', 4), ('AAD', 4)

How can I delete id 1 and all of its children without using cascade delete?

Upvotes: 2

Views: 95

Answers (1)

Thom A
Thom A

Reputation: 95906

You would have to use an rCTE (recursive Common Table Expression) to recurse through the hierachy. Then you can JOIN that data to your table and delete the relevant rows:

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;

Note that unlike some some examples you may have seen, such as where a CTE is used to DELETE duplicate rows, a rCTE is not updatable (due to the use of the UNION). As such you can't simply perform a DELETE operation on the CTE at the end (DELETE FROM rCTE) and you have to use it as a JOIN.

DB<>Fiddle

Upvotes: 2

Related Questions