mysql UPDATE field to NULL when referenced parent doesn't exists in same table

Question

I need to write a tricky query and after a lot of researches, I can't find a solution that matches my situation.

I'm working on an existing database where I need to reference committees which can have sub committees so the committee table looks as follows:

+----+--------------------+------------------+
| id | name               | parent_id        |
+----+--------------------+------------------+
|  1 | comm1              | NULL             |
|  2 | comm2              | 1                |
|  3 | comm3              | 1                |
|  4 | comm4              | 5                |
+----+--------------------+------------------+

I need to add a FK so that the parent_id field references the id field of the actual parent

ALTER TABLE committee
ADD CONSTRAINT fk_parent_id 
FOREIGN KEY (parent_id )
REFERENCES committee(id);

But that fails (Cannot add or update a child row: a foreign key constraint fails) because in the data some parent_id field reference committee that have been deleted.

So to be able to add this constraint, I want to set to NULL the parent_id field of committee referencing a non-existant committee as parent. I want to do something like:

UPDATE committee c1
SET c1.parent_id = NULL
WHERE NOT EXISTS
        (
        SELECT * 
        FROM committee c2
        WHERE c2.id = c1.parent_id
        );

But obviously that doesn't work because I can't reference c1 in the sub-query.

Is there a working solution to achieve that? Thanks in advance for your help

Answer 1

You can do "self-join" utilizing Left Join instead:

UPDATE committee AS c1
LEFT JOIN committee AS c2 ON c2.id = c1.parent_id 
SET c1.parent_id = NULL
WHERE c2.id IS NULL 

We can optimize this further, by not considering those row(s), where parent_id is already null.

UPDATE committee AS c1
LEFT JOIN committee AS c2 ON c2.id = c1.parent_id 
SET c1.parent_id = NULL
WHERE c2.id IS NULL AND 
      c1.parent_id IS NOT NULL