Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting rows recursively in a self-referencing table using a CTE. How does the process take place?

I'm working on a side project, and in order to delete a row and all its descendants in a self-referencing table, I'm using a recursive CTE like this inside a trigger:

CREATE TRIGGER dbo.tr_Comment_Delete
    ON dbo.Comment INSTEAD OF DELETE
AS
    ;WITH IDs AS (
       SELECT id FROM DELETED
       UNION ALL
       SELECT c.id
       FROM Comment AS c INNER JOIN IDs AS i 
        ON c.parent_comment_id = i.id
    )
    DELETE FROM Comment
    WHERE id IN (SELECT id FROM IDs);
GO

This is the self-referencing table

enter image description here

Although I have this code working as expected, it is one of those cases in which you do something, but you're not quite sure how it works.

To be more precise, what I'd like to know is how it is that by using this recursive CTE(IDs) I'm able to avoid referential integrity errors when I try to delete a comment that has child comments?

What is it the process/order in which the comments are deleted?

Take this hierarchy of comments as an example:

3-> 8-> 13 

Here the comment of id 3 is the root comment. Comment 8 is a reply to comment 3,just like comment 13 is a reply to comment 8.

How does the deletion process actually take place?

P.S. I tried adding a table in which I inserted the Ids as they were calculated. Unfortunately I can't make sense of it. These are the results of such table:

id  ins-date
3   2017-09-12 11:48:38.037
8   2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
8   2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
like image 201
eddy Avatar asked Sep 12 '17 21:09

eddy


People also ask

How does CTE recursion work?

A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results.

When working with recursive CTE which option is used?

You can define the maximum number of recursions for CTE, using the MAXRECURSION option. Set the value of MAXRECURSION to 0, if you don't know the exact numbers of recursions.

What is CTE in SQL Server and its uses?

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.


1 Answers

I suppose you see complexity where it does not exists.

Your mistake is:

Deleting rows recursively in self-referencing CTE

There is no such thing as recursive DELETE. Only SELECT can be.

So processing is simple as:

  1. Calculate all rows for deletion in SELECT with recurcive CTE

  2. DELETE them all with one operation

That's all

like image 106
Alex Yu Avatar answered Nov 08 '22 20:11

Alex Yu