Sql: delete a subtree table (id, Parentid), delete an element with all its 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 the item with all its children and children of children, etc. Does anyone know how?

Answer 1

AFAIK, SQL SERVER does not like cascading deletions for hierarchical relationships. That way you can make both CTEs (as Oded pointed out) or a solution with a recursive trigger (something like this ). But I suppose CTE is simpler.

See, here is a 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 define a recursive CTE, and then delete the records from the [Foo] table, starting from the most child records (hightest Level , so the top node will be deleted in the last turn).

Answer 2

You can write a recursive CTE , the anchor will be the starting identifier.