Remove assigned users from database table

Question

I have a table:

| staff_id | leader_id |   team_role  | importance |
+          +           +              +            +
| 1001037  |  1000321  |      HOD     |      1     |
| 1001037  |  1000322  |      HOD     |      1     |
| 1001037  |  1001037  |  Supervisor  |      2     |
| 1001094  |  1001037  |    Checker   |      3     |
| 1001075  |  1001037  |    Checker   |      3     |
| 1001096  |  1001075  | Squad Leader |      4     |
| 1000393  |  1001094  | Squad Leader |      4     |
| 1000465  |  1001094  | Squad Leader |      4     |
| 1000585  |  1001075  | Squad Leader |      4     |
| 1000664  |  1000585  | Team Member  |      5     |
| 1000583  |  1000585  | Team Member  |      5     |
| 1000570  |  1000465  | Team Member  |      5     |
| 1000316  |  1000465  | Team Member  |      5     |

In php it look like this:

If I want to remove user with staff_id = 1001075 I need to remove all assigned to this user ids. So it will be ids where 1001075 is leader_id. I am able to this but I also need to remove users going down to the tree so where leader_id= 1001075 is will be 1000585 Then I need to remove users where leader_id= 1000585

In the end I need to delete these users with staff_id:

How can I do that?

Have you written anything in an attempt to achieve this goal — RiggsFolly, Sep 06 '17 at 08:52

score 1 · Accepted Answer · answered Sep 06 '17 at 08:52

There are two ways to do this:

Recursively query for all the ids to find out all the children and delete the rows one by one. Have a look at this answer for approaches to recursively query the ids.
Run an ALTER TABLE script and make leader_id a foreign key, referencing to staff_id of the same table, and define with ON DELETE CASCADE. By this way, when you delete a leader row, it will delete all the subsequent rows, e.g.:

ALTER TABLE table ADD CONSTRAINT fk_leader_id FOREIGN KEY (leader_id) REFERENCES table(staff_id) ON DELETE CASCADE;

I would prefer the second approach.

Remove assigned users from database table

1 Answers1