[Resolved] Does anyone now how to compare 2 tables?

airric

I need to compare 2 tables and delete from table 2 what no longer exists in table 1.

I have tried many things but I haven't done it successfully!

I would appreciate any help I can Get.

Thanx, Airric

Roger_Ramjet


DELETE * FROM table2 LEFT JOIN table1 USING(id) WHERE table1.id IS NULL

Look up the mysql manual for JOIN SYNTAX next time, the solution is obvious from the text and examples.

mtmosier

Well, I'm not ashamed to say that I read the manual and I didn't find it so obvious. The following should work on mysql 4.0.2 and above. (Tested on 4.0.23.)

DELETE FROM table2 USING table2 LEFT JOIN table1 USING (id) WHERE table1.id IS NULL;

Roger_Ramjet

Then you did not read far enough down the page for JOIN SYNTAX

From the manual

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;

So replace SELECT with DELETE and you have the query

airric

I had the same problem with the manual before I posted.
perhaps I just need to unplug and refocus. Thanx I'l give it a go!

Thank you for your feedback.