Delete Child Records with Missing Parent

Question

I'm attempting to create a SQL query to delete child records when the parent has been deleted.
The thing is, the children and parents are both stored in the same table.
Here's the (simplified) data model:

ThingTable

    ID  |  Name  | ParentID
   1000 | Thing1 |   NULL
   1001 | Thing2 |   1000
   1002 | Thing3 |   1000
   1003 | Thing4 |   1000
   1004 | Thing5 |   1003

ChildThingTable

    ID  |  Color  
   1001 |  Blue
   1002 |  Black
   1003 |  Green
   1004 |  Red  

Assuming ID 1000 (the parent) was deleted, I'd need to delete the corresponding records from ChildThingTable as well as ThingTable.

My only restriction is that I cannot use a trigger or alter the underlying database structure in any way.

Here's my pseudocode that I've worked out, but I'm having difficulty translating it into SQL:

1. Delete from ChildThingTable where the matching record in ThingTable does not have a parent, based on ID and ParentID.
2. Delete from ThingTable where the matching record does not have a parent.

Any assistance would be greatly appreciated!

Answer 1

You can use a Common Table Expression to recurse the

-- Begin Create Test Data

SET NOCOUNT ON 

CREATE TABLE #ThingTable (
ID INT NOT NULL,
[Name] VARCHAR(255) NOT NULL,
[ParentID] INT NULL

)


CREATE TABLE #ChildThingTable (
ID INT NOT NULL,
[Color] VARCHAR(255) NOT NULL,

)

INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1000,'Thing1',NULL)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1001,'Thing2',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1002,'Thing3',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1003,'Thing4',1000)
INSERT INTO #ThingTable (ID,[Name],ParentID) VALUES (1004,'Thing5',1003)

INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1001 , 'Blue')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1002 , 'Black')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1003 , 'Green')
INSERT INTO #ChildThingTable ( ID, Color ) VALUES  ( 1004 , 'Red')

SET NOCOUNT OFF
GO
-- End Create Test Data

-- This is a batch, but could easily be a stored procedure.  
DECLARE @InputID INT 
SET @InputID = 1000;

SET NOCOUNT ON
DECLARE @Temp TABLE(ID INT NOT NULL);

WITH ThingCTE (ID, ParentID, [Level])
AS
(
SELECT tt1.ID, tt1.ParentID, 1 AS [Level]
FROM #ThingTable tt1
WHERE tt1.ID = @InputID
UNION ALL
SELECT tt2.ID, tt2.ParentID, tc1.[Level]+1
FROM #ThingTable tt2
JOIN ThingCTE tc1 ON (tt2.ParentID = tc1.ID)
)
INSERT INTO @Temp
        ( ID )
SELECT ID
FROM ThingCTE

SET NOCOUNT OFF

DELETE ctt
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.* 
FROM #ChildThingTable ctt
JOIN @Temp t ON (ctt.ID = t.ID);

DELETE tt 
-- Output is for debug purposes, should be commented out in production.
OUTPUT Deleted.* 
FROM #ThingTable tt
JOIN @Temp t ON (tt.ID = t.ID)

DROP TABLE #ChildThingTable;
DROP TABLE #ThingTable;