How to select all records in MySql that does not have a parent by parent_id?

Question

MySQL

Good afternoon, there is a table where more than one million records. The table is a tree with parents and children by parent_id
But there is garbage in it, these are records that do not have a parent. How to remove them?
Table structure:
id parent_id name
1 0 entry
1 1 entry1
1 7 garbage

Answer 1

First, check that it returns the correct records.

SELECT GROUP_CONCAT(t1.id) FROM table_name as t1 left join table_name as t2 on t2.id = t1.parent_id where t1.parent_id > 0 and t2.id is null

Then garbage removal:

DELETE t1 FROM table_name as t1 left join table_name as t2 on t2.id = t1.parent_id where t1.parent_id > 0 and t2.id is null

table_name is the same table name.