Page 1 of 1

Remove obsolete entries

Posted: Thu Dec 15, 2005 10:01 pm
by Skara
Erhm.. I went back and deleted quite a few entries in one table. Now I have entries in another table that are obsolete, so to speak.

I need a query that does..

DELETE FROM tb2 WHERE tb2.id <does not exist in tb1 as tb1.id>;

In other words, if it can't find an entry in tb1 with the id tb2.id, delete the tb2.id row. That make sense?

Posted: Thu Dec 15, 2005 10:25 pm
by neophyte
Ummm I don't know how to write that query but you might try writing a php script that dumps both tables. Do a comparison of array a vs. b. Remove identical id's from b until there is nothing left but unmatched id's. Then you'd have your list. If there is a way to do this with a single query I'd like to see it.

Posted: Fri Dec 16, 2005 3:33 am
by onion2k
Very Important: Backup your data before doing this sort of thing.

Assuming you're using MySQL 4.1 or higher..

Code: Select all

delete from table1 where id not in ( select id from table2 );

Posted: Fri Dec 16, 2005 7:02 am
by timvw
They keyword is "foreign keys". If you create a table or after you have created you can add some rules what should happen on update / delete. You can choose for restrict (can't delete parent if there are still siblings), cascade (remove all siblings and then remove parent) or set null (set the child reference to NULL).

http://dev.mysql.com/doc/refman/5.0/en/ ... aints.html.