CODEWITHSUNDEEP
sqlsql-server
vishal
vishal

Reputation: 207

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

Top-level menu 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 parentId.

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.

Upvotes: 1

Views: 1754

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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 2 and 4 will be deleted.

Upvotes: 0

TechDo
TechDo

Reputation: 18659

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)

Upvotes: 4

Related Questions