There is a table. id | parent_id.

Some parent_id have no parent. How to remove orphans?

For example, there is an entry with parent_id = 10 , but its parent with id = 10 is not. As I understand it, something like

 DELETE FROM table t LEFT JOIN table t1 ON t.id = t1.parent_id WHERE t.id IS NULL
  • parent_id = NULL? - Mirdin
  • one
    And what does "no parent" mean? parent_id = NULL or is it not NULL, but refers to a non-existing entry? - Mike
  • There is no record with such id, and with parent_id there is - Oleg Grigoriev
  • Fun fact: after you delete these orphan records, other records can become orphans. This way you can repeat deletions recursively until only records with parent_id = NULL remain. - Roman

2 answers 2

 delete from test3 where id in( select id from ( select id from test3 A where not exists(select 1 from test3 B where B.id=A.parent_id) ) as X ) and parent_id is not NULL

Excessive nesting of the query and alias (X) is necessary because MySQL would otherwise not allow access to the same table from which deletion occurs.

UPD: I got a little overwhelmed in the comments about the left join, the variant with it is working and it looks clearly shorter:

 delete A.* from test3 A left join test3 B on B.id=A.parent_id where B.id is NULL and A.parent_id is not NULL

Speed ​​of work is worth comparing

  • Such a request will probably put me server =)) About 200,000 entries. What do you think? - Oleg Grigoriev
  • @Oleg Grigoriev And your left join will produce the same as not exists. Try to start the speed of the subquery receiving the list. - Mike
  • Wouldn't these queries also parent_id = NULL entries with parent_id = NULL ? - Roman
  • @Mike, thank you very much for the answer. Works, but not enough parent_id <> NULL. And then the parents of the upper level will also be removed, who, of course, have no parents. A JOIN is not working. Or the whole snag in A. *, check ... - Oleg Grigoriev
 DELETE FROM myTable WHERE parent_id NOT IN (SELECT id FROM parents);
  • Entries in the same table - Oleg Grigoriev
  • what is the problem? use one table in the query - tCode
  • Does not work. MySQL does not otherwise allow access to the same table from which deletion occurs, as @Mike said in its answer. Well, the documentation, I think, said the same thing. - Oleg Grigoriev
  • And in principle, the use of NOT IN (a large number of records) is a bad practice. I do not argue, there are cases when you can not do without it. But here it can be different. - Alexander Belinsky