SQLTeam.com | Weblogs | Forums

Cascading delete


#1

I have a parent record for that there are 12 levels of child records. if i delete parent record then the child records also must delete . How can i delete is there any Query for that?

Thanks And Regards
Nagaraju


#2

You could use the Cascading Delete option of a Foreign Key

In code I don't think there is a way to do it other than:

BEGIN TRANSACTION
DELETE D12
FROM Table1 AS D1
    JOIN Table2 AS D2
        ON D2.SomeID = D1.SomeID
...
    JOIN Table2 AS D12
        ON D12.SomeID = D11.SomeID
WHERE D1.Col1 = 'ParentID'

then repeat for D11 ... D1

Then COMMIT

I think I would be inclined to get all the Key Fields into a #Temp table, first, and then use that to delete the individual tables, Child12 first.

BEGIN TRANSACTION
SELECT D1.SomeID AS [D1_ID], D2.SomeID AS [D2_ID, ...
INTO #TEMP
FROM Table1 AS D1
    JOIN Table2 AS D2
        ON D2.SomeID = D1.SomeID
...
    JOIN Table2 AS D12
        ON D12.SomeID = D11.SomeID
WHERE D1.Col1 = 'ParentID'

DELETE D12
FROM #TEMP AS T
    JOIN Table12 AS D12
        ON D12.SomeID = T.D12_ID
...
DELETE D1
FROM #TEMP AS T
    JOIN Table1 AS D1
        ON D1.SomeID = T.D1_ID
COMMIT

#3

Is the parent and child records on the same table ?