CODEWITHSUNDEEP
sql-serverself-join
Álvaro García
Álvaro García

Reputation: 19396

how to delete a tree in a self referencing table?

I have the following table:

MyTree(IDNode, IDRootNode, IDParentNode, Left, Right, Depth, ...)

IDNode is the primary key that identiy the node. IDRootNode is the root node. IDParentNode is the parent node of the node. Left and Right is the position of the node. IS not important for the delete.

I see the following code, is a trigger, that let delete registers from a self referencing table:

CREATE TRIGGER MyTable_OnDelete ON MyTable
INSTEAD OF DELETE
AS 
BEGIN

  SET NOCOUNT ON;

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.ParentId

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.Id

END

If I am not wrong, first delete the childs of a node that I want to delete, and later delete the node. But if a child has childs, are deleted too or only delete the childs of the node and not the childs of the childs?

And with recrsive CTE how would it be?

Thanks.

Upvotes: 1

Views: 1258

Answers (2)

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

You can do it with recursive CTE like this:

;WITH cte AS (
    SELECT  IDNode
    FROM    Deleted
    UNION ALL
    SELECT  IDNode
    FROM    MyTree t
            JOIN cte c ON t.IDParentNode = c.IDNode
) 
DELETE  t
FROM    MyTree t
        JOIN cte c ON t.IDNode = c.IDNode;

Better yet, use HierarchyId (if on SQL Server 2008)!

Upvotes: 2

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

If each node in the tree has the IDRootNode column populated with the id of the tree to which the node belongs, you could delete the tree using the following SQL:

DELETE MyTable WHERE IDRootNode = @RootNodeId

Upvotes: 2

Related Questions