Remove from table used in where clause

Question

I am writing a small script to sync 2 MySQL tables (t1 will be "mirrored" with t2)

In one step, I would like to delete lines inside t2 that were deleted 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 forbids me to use t2 at the same time in deletion and in selection (the sound is logical by the way)

Of course, I can split the request into 2 requests: first select the ids and then remove the rows with those ids.

My question is, do you have a cleaner way to remove a row from t2 that no longer exists in t1? with one request?

+2

Eric May 10 '10 at 20:05

3 answers

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

0

Ike Walker May 10 '10 at 20:09

If you use left join t1 versus t2, the values for 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;

0

Mike pelley May 10 '10 at 20:16

JYelton · Accepted Answer · 2010-05-10T20:10:12+0000

This query joins two tables and only selects those that don't have a partner in the new table, so you can drop them in one go:

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