Remove obsolete entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Remove obsolete entries

Post 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?
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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 );
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
Post Reply