CODEWITHSUNDEEP
mysql
SimaPro
SimaPro

Reputation: 1234

Delete all descendants of an entry (MySQL)

I have a table with, let's say, the following columns:

Name , Parent Name, ID

Let's also say that there are three entries where Parent Name is Null (meaning they are the top-most parent) - F_one, G_one, and H_one.

If I want to delete all the descendants of one of those parents (G_one, why not?) meaning all the children of G_one, all the children of those children, and the children of those, and so on all the way until the terminal level where, that row's Name does not exist as a Parent Name for any other entry.

Is that possible to be done easily, maybe with a single query?

Bonus, is there a way to select all of the G_one lineage so I can manipulate it to my whim and will?

Can assume: -No Children are shared among parents

Cannot assume: -A discrete or even consistent number of sub-levels.

Upvotes: 0

Views: 198

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

As @Marc B's suggestion, a FORIEGN KEY with ON DELETE CASCADE would achieve this.

If you haven't one, you can add it now:

  1. If there is a UNIQUE constraint on Name (I assume the PRIMARY key os ID), skip thi sstep. If there ism't one, create it:

    ALTER TABLE tableX
  ADD CONSTRAINT unique_Name
    UNIQUE (Name) ;

  2. If the previous step succeeded, add the FOREIGN KEY:

    ALTER TABLE tableX
  ADD CONSTRAINT fk_Name_ParentName
    FOREIGN KEY (ParentName)
       REFERENCES tableX (Name)
       ON UPDATE CASCADE
       ON DELETE CASCADE ;

  3. If the previous step succeeded, you can now delete your rows with one statement:

    DELETE
FROM tableX
WHERE ParentName = 'G_one' ;

    This should result in: Y rows affected.

Upvotes: 1

Nathan Andrew Mullenax
Nathan Andrew Mullenax

Reputation: 824

I can't test this, but I think something like this might work:

CREATE TRIGGER casc_del AFTER DELETE on tblName
FOR EACH ROW 
    DELETE FROM tblName 
    WHERE tblName.parent_name is not null 
    AND tblName.parent_name NOT IN (SELECT name FROM tblName)

More about triggers in MySQL can be found here. Note: this approach would only work in 5.02 or later.

Upvotes: 0

Related Questions