0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

DECLARE @T Table (ID int, Name varchar(50), ParentId INT);
DECLARE @itemToDelete INT = 4;
INSERT INTO @t VALUES
(1  ,'Home',                    0),
(2  ,'Products',                0),
(3  ,'SubProduct1',         2),
(4  ,'SubProduct2',         2), 
(5  ,'Subsub',           4),
(6  ,'ii. ......',          4),
(7  ,'About',                   0);

;WITH ParentChildsTree
AS
(
    SELECT ID, Name, ParentId
    FROM @T
    WHERE Id = @itemToDelete
    UNION ALL
    SELECT t.ID, t.Name, t.ParentId
    FROM ParentChildsTree c
    INNER JOIN @T t ON c.ParentId = t.Id
)
DELETE FROM @T 
WHERE ID IN (SELECT ID FROM ParentChildsTree);



sELECT * FROM @T;

fork query

edited apr 23 anonymous

created sep 27 12 Mahmoud Gamal