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 |
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;