sql: delete a subtree table(id, Parentid) , delete a item with all his children

Question

I have a table like this

foo(id, parentId) 
-- there is a FK constraint from parentId to id

and I need to delete an item with all his children and children of the children etc. anybody knows how ?

Answer 1

AFAIK, SQL SERVER doesn't like cascade deletes for hierarchal relationships. So you could do both CTE (as Oded mentioned) or a solution with a recursive trigger (somehow like this). But I suppose, CTE is easier.

See, here is solution using CTE:

CREATE PROC deleteFoo 
@id bigint
as
WITH Nodes ([Id], [ParentId], [Level]) 
AS (
    SELECT  F.[Id], F.[ParentId], 0 AS [Level]
    FROM    [dbo].Foo F
    WHERE   F.[Id] = @id

    UNION ALL

    SELECT  F.[Id], F.[ParentId], N.[Level] + 1
    FROM    [dbo].Foo F
        INNER JOIN Nodes N ON N.[Id] = F.[ParentId]
)

DELETE
FROM    Foo
WHERE   [Id] IN (
    SELECT  TOP 100 PERCENT N.[Id] 
    FROM    Nodes N
    ORDER BY N.[Level] DESC
)

firstly, we're defining recursive CTE, and then deleting records from the [Foo] table beginning from the very child records (hightest Level; so, the top node will be deleted in the last turn).