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
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
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
Is the parent and child records on the same table ?