Mysql delete row where parent does not exist

Question

I have a table called elements with columns "itemID" and "parentID", where parentID is the identifier from another row in the table. I want to delete rows where the parentID row no longer exists.

Here 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 cannot specify the elements of the target table to update in the FROM clause

EDIT: It would be nice if the elements associated with the elements deleted by this query were also deleted on request, is this possible in sql or should I encode it?

Answer 1

Using subqueries is inefficient; 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 

the syntax of multiple tables allows you to specify a join in the delete command, so you can join multiple tables and delete only one. That is, DELETE t1 FROM t1 join t2 .... joins t1 and t2 , but deletes only the corresponding rows from table t1 .

As for your question, “it would be nice if the elements associated with the elements deleted by this query were also deleted on request, is this possible in sql or should I encode it?”

I think this will end in cascade - a deleted record can be the parent of another record, so delete it as well, but it can also be the parent of another record, so you need to delete it too, etc. I think it is not possible to query this entire cascade with a single SQL query.

So, I think the easiest solution is to rerun the specified request until the rows are deleted.

Answer 2

Request will be

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

Answer 3

try this little witchcraft ...

 delete from FooTable f1 where f1.parent_id not in (select f2.item_id from FooTable f2); 

Of course, this query has a slight problem, if you may have noticed that it can generate new FooRecords without a parent (this means that their parent can be deleted during the execution of the request).

Fulfill the request several times manually, if you do not need something automatic, or place it inside the loop and stop the loop when the number of deleted records is 0.