MySQL : delete from table that is used in the where clause

Question

I am writing a small script to synchronize 2 MySQL tables ( t1 to be 'mirrored' to t2 )

In a step I would like to delete rows inside t2 that has been delete in t1 with the same id.

I tried this query :

delete from t2 where t2.id in 
    ( select t2.id left join t1 on (t1.id=t2.id) where t1.id is null )

But Mysql forbid me to use t2 in the same time in the delete and in the select (sound logical by the way)

Of course, I can split the query into 2 queries : first select IDs, then delete rows with these IDs.

My question : do you have a cleaner way to delete row from t2 that does not exist anymore in t1 ? with one query only ?

Answer 1

delete t2.* 
from t2 
left join t1 on (t1.id=t2.id) 
where t1.id is null;

Answer 2

This query joins up the two tables and only selects those that don't have a partner in the new table, thus allowing you to delete them in one go:

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

Answer 3

If you use a left join of t1 against t2, values for the t1 fields will be NULL for any rows that don't have a match in t2, so this should work:

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