Mysql delete row where parent does not exist

Question

I have a table called items with collumns "itemID" and "parentID" where the parentID is an ID from another row in the table. I want to remove the rows where the parentID-row doesn't exist anymore.

This is what i came up with:

DELETE FROM items
WHERE (SELECT COUNT(1)
       FROM items as parent
       WHERE items.parentID=parent.itemID)=0

But i get the following error: You can't specify target table 'items' for update in FROM clause

EDIT: it would be nice if items linking to items removed by this query will also be removed by the query, is this possible in sql or should I code it?

Answer 1

Using subqueries is inefficent; use left join with multiple-table syntax:

DELETE items 
FROM items
    left join items as parent 
        on items.parentID = parent.itemID
WHERE parent.itemID is NULL

multiple-table syntax allows you to specify join in the delete command - so you can join multiple tables and only delete from one. I.e. DELETE t1 FROM t1 join t2 .... will join t1 and t2 but only delete the corresponding rows from table t1.

As for your question "it would be nice if items linking to items removed by this query will also be removed by the query, is this possible in sql or should I code it?"

I think this would end up with cascade - the deleted record could be parent of another record, so delete that one also, but that one could be also parent of another record, so you need to delete it too, etc. etc. I think it is not possible to query all this cascade by a single SQL query.

So I think the easiest solution is to re-run the above query until no rows are deleted.