For deleting orphaned children in a relational database I tried deleting those children using a ‘LEFT JOIN’ on the ‘mother’ table to find the children. With the select I used the normal approach and found all those poor kids. Changing the SELECT * into DELETE did not work. You’re not allowed to DELETE on the used table. First I thought a subquery could help me, NOT 🙂

The solution is to use the keyword USING.

An example:

DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL

In this way you can use as many joined tables for deleting, without affecting those joined tables

After testing I found out this also worked for me:

DELETE t1 FROM t1 LEFT JOIN t2 ON t2.id = t1.id WHERE t2.id IS NULL

I didn’t use the ‘FROM’ and don’t use aliases for your table you wish to clean. That won’t work!