Delete all level child item using sql query
I have a table where I have menus listed where I can insert and delete.
Structure goes like:-
ID Name ParentId 1 1. Home 0 2 2. Products 0 3 a. SubProduct1 2 4 b. SubProduct2 2 5 i. Subsub 4 6 ii. ...... 4 7 3. About 0
ParentId is always 0 as displayed in 1, 2 and 7.
Child level items would have
ParentId of their parent for ex.
Subproduct has 2 as its
When I delete menu item that time all level child item should be delete irrespective of there levels using SQL query.
There can be any number of levels
The levels can go upto subsubsubsub…… any number.
2 Solutions collect form web for “Delete all level child item using sql query”
How about this query:
DECLARE @DelID INT SET @DelID=1 ;WITH T(xParent, xChild)AS ( SELECT ParentID, ChildId FROM Table WHERE ParentID=@DelID UNION ALL SELECT ParentID, ChildId FROM TABLE INNER JOIN T ON ParentID=xChild ) DELETE FROM TABLE WHERE ParentID IN (SELECT xParent FROM T)
You can use a common table expression to get all the heirarchy items from the item you want to delete to the end of the tree hten
;WITH ParentChildsTree AS ( SELECT ID, Name, ParentId FROM MenuItems WHERE Id = @itemToDelete UNION ALL SELECT ID, Name, ParentId FROM ParentChildsTree c INNER JOIN MenuItems t ON c.ParentId = t.Id ) DELETE FROM MenuItems WHERE ID IN (SELECT ID FROM ParentChildsTree);
Here is a Demo.
For example if you pass a parameter
@itemToDelete = 4 to the query the the items with ids
4 will be deleted.