CODEWITHSUNDEEP
sqlsql-serversql-server-2008hierarchical-data
eyalewin
eyalewin

Reputation: 360

How to delete all nodes of given root in sql adjacency list

Having this table:

enter image description here

I want to delete the root node, by sending a query ID=1 and I want that all the subnodes of this id will be deleted as well.

how can I do it?

Upvotes: 0

Views: 664

Answers (2)

user330315
user330315

Reputation:

This can be done using a recursive common table expression.

To get all children of one node you can use:

with tree as (
  select id, parent
  from eyalewin
  where id = 1
  union all
  select c.id, c.parent
  from eyalewin c
    join tree p on p.id = c.parent
)
select *
from tree;

This can then be used to delete all of them:

with tree as (
  select id, parent
  from eyalewin
  where id = 1
  union all
  select c.id, c.parent
  from eyalewin c
    join tree p on p.id = c.parent
)
delete from eyalewin
where id in (select id 
             from tree);

Upvotes: 3

gotqn
gotqn

Reputation: 43636

Let's say you have the following data:

enter image description here

and you want to delete record 2.1 and all of his descendants (in this case 2.1.3 only). You can use the following code to get the IDs that needs to be deleted:

WITH DataSource ([ID], [HierarchyLevel]) AS
(
    SELECT [ID]
          ,CAST(REPLACE('/' + [Name] + '/', '.', '/') AS HIERARCHYID)
    FROM @DataSource
)
SELECT [ID]
      ,[HierarchyLevel].ToString()
FROM DataSource
WHERE [HierarchyLevel].IsDescendantOf('/2/1/') = 1;

enter image description here

Basically, we convert your record to hierarchy id type and use the build-in IsDescendantOf function to get all childs of the node.

This is full working example:

DECLARE @DataSource TABLE
(
    [ID] TINYINT
   ,[Name] VARCHAR(12)
   ,[Owner] VARCHAR(12)
   ,[Parent] INT
);

INSERT INTO @DataSource ([ID], [Name], [Owner], [Parent])
VALUES (1, '1', '1', NULL)
      ,(2, '1.1', '1.1', 1)
      ,(3, '1.1.1', '1.1.1', 2)
      ,(4, '2', '2.1', NULL)
      ,(5, '2.1', '2.1', 4)
      ,(6, '2.2', '2.2', 4)
      ,(7, '2.1.3', '2.1.3', 5);


WITH DataSource ([ID], [HierarchyLevel]) AS
(
    SELECT [ID]
          ,CAST(REPLACE('/' + [Name] + '/', '.', '/') AS HIERARCHYID)
    FROM @DataSource
)
DELETE @DataSource
FROM @DataSource DS
INNER JOIN DataSource DS1
    ON DS.[ID] = DS1.[ID]
WHERE [HierarchyLevel].IsDescendantOf('/2/1/') = 1;

SELECT *
FROM @DataSource;

Upvotes: 2

Related Questions