sql – Deletion in closure table with multiple same paths

I have the following hierarchical structure:

A -> E -> C -> D
|
|
|-> B -> D

Here is the closure table I’ve come up with:

| Ancestor | Descendant | Depth |
|    A     |     A      |   0   |
|    B     |     B      |   0   | 
|    C     |     C      |   0   | 
|    D     |     D      |   0   | 
|    E     |     E      |   0   | 
|    A     |     E      |   1   | 
|    A     |     B      |   1   | 
|    A     |     C      |   2   | 
|    E     |     C      |   1   | 
|    A     |     D      |   3   | 
|    E     |     D      |   2   | 
|    C     |     D      |   1   | 
|    A     |     D      |   2   | 
|    B     |     D      |   1   | 

I want to remove the link between B and D, and therefore I want to delete the link between A and D (the one of depth 2). The problem is that I don’t want to delete the link between A and D of depth 3 since I didn’t delete the link between C and D.

For the moment, here is the SQL statement to list the links I want to delete:

SELECT link.ancestor, link.descendant, link.depth
FROM closure_table p,
     closure_table link,
     closure_table c
WHERE p.ancestor = link.ancestor
  AND c.descendant = link.descendant
  AND p.descendant = B
  AND c.ancestor = D;

but this statement give me rows I don’t want to delete:

| Ancestor | Descendant | Depth |
|    A     |     D      |   2   |
|    A     |     D      |   3   |  <- As said before, I want to keep this one
|    B     |     D      |   1   | 

Answer

I think I’ve found the solution, for those who are interested:

declare @Descendant nchar(10) = 'D';
declare @Ancestor nchar(10) = 'B';

with cte as
(
  select Ancestor, Depth
  from closure_table
  where Descendant = @Descendant
  and Ancestor = @Ancestor
  and Depth = 1
union all
  select r.Ancestor, l.Depth + 1 as Depth
  from cte as l
  join closure_table as r on r.Descendant = l.Ancestor
  where r.Depth = 1
)
delete closure_table
from closure_table
join cte on cte.Ancestor = closure_table.Ancestor and cte.Depth = closure_table.Depth
where closure_table.Descendant = @Descendant;