Is there a better way to construct this query?

Question

I have a table like

      CommentPaths
============================
ancestor_id | descendant_id
============================
     1            1
     1            2
     1            3
     1            4
     1            5
     1            6
     1            7
     2            2 
     2            4 
     2            5
     4            4
     5            5
     3            3
     3            6
     3            7 
     6            6
     7            7

intended to represent a tree structure like

        1
      /   \
     2     3
    / \     \
   4   5     6
            /
           7

Suppose I delete 2. Then I need to delete the records pointed to below.

      CommentPaths
============================
ancestor_id | descendant_id
============================
     1            1   
     1            2   <-------------
     1            3
     1            4
     1            5
     1            6
     1            7
     2            2   <-------------
     2            4   <-------------
     2            5   <-------------
     4            4   <-------------
     5            5   <-------------
     3            3
     3            6
     3            7 
     6            6
     7            7

In other words, when deleting k from the tree I need to

Delete all rows where ancestor_id equals k
Delete all rows where the descendant_id equals there ancestor_id is one of the ones from 1.

So my query would look something like

SELECT descendant_id FROM CommentPaths WHERE ancestor_id=2 AS descs
DELETE FROM CommentPaths WHERE ancestor_id IN descs
DELETE FROM CommentPaths WHERE descendant_id IN descs

or is there a fancier, more compact way of doing it????

@KoebmandSTO That's the way it's presented on pg 36 of this book I'm reading https://pragprog.com/book/bksqla/sql-antipatterns. I never understood why either. — user5648283, Jan 17 '16 at 04:10
Trashing the book and making a structure where each entry only appears once will be more compact. Does your code even delete the lines 4 4 and 5 5? — Koebmand STO, Jan 17 '16 at 04:21
You need a recursive query. Other wise if you delete 3, only 6 will be delete but not 7, check here http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy — Juan Carlos Oropeza, Jan 17 '16 at 04:26
@JuanCarlosOropeza No, it won't. My table is not a parent-child relationship, but an ancestor-descendant relationship. — user5648283, Jan 17 '16 at 04:36

score 1 · Answer 1 · edited May 23 '17 at 11:44

-- delete relation from tree
with tree (commentTreeID, ancestor, descendant , path,  src) as 
(
select id,  ancestor_id,  descendant_id , cast ( '-'+ cast(id as varchar(2000)) +'-' as varchar(2000))   , 0from 
CommentPath ct
where ct.ancestor_id = 2
union all
select CT.Id, CT.ancestor_id,  CT.descendant_id ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 1
from tree t
join CommentPath CT 
 on CT.ancestor_id = t.descendant and
  CHARINDEX (cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) = 0 and
  t.src != 2
union all
 select CT.Id, CT.descendant_id,  CT.ancestor_id ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 2
from tree t
join CommentPath CT 
 on CT.descendant_id = t.descendant and
 CHARINDEX(cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) =0  and
   t.src != 2
 ) 
 delete CT 
  from CommentPath CT 
  join tree t 
  on t.commentTreeID = CT.ID;

The first join is used to go down on tree (src = 1) .
The second join is used to go up on tree (src = 2 ).

When we go up we don't want to go down or up again = > t.src != 2 makes it happen.

CHARINDEX (... ) protects us from loops.

Please refer to my solution that I have posted already there. It shows also how to handle Comment table.

And here link in SQL Fiddle to show some sample that shows what it deletes.

Is there a better way to construct this query?

1 Answers1