How to delete MySQL orphaned records?

Question

I've come to realize that there is data existing in some tables in my database for userids that have been deleted from the user table. Is there a simple way to remove records from other tables for userids that don't exist in my user table any more?

Answer 1

Favorite Answer

First, backup your database.

Then

DELETE FROM table WHERE userid NOT IN (SELECT userid FROM usertable);

Source(s): http://ocaoimh.ie/2004/09/16/mysql-finding-the-rec...

Answer 2

Do you have MySQL SQL Browser installed? You can download it free from the MySQL site. That would be the simplest way to delete table records. With the SQL Browser, you can execute SQL code. You can have a look at the records with the select statement, and delete them with the delete statement. In your case you would want something like:

select * from rcdtable where userid = nnn;

replacing nnn with the actual userid. This will return a result table with the records you may want to delete. Then delete them with a statement like:

delete from rcdtable where userid = nnn;

be careful with the delete command, it can do a lot of damage. For instance if you execute something like 'delete from rcdtable', it will delete everything in the table.

Another question you may want to ask is whether it is really worth deleting the records. They rarely take much space, and there can be problems in applications if data is missing, even data that may never be accessed. It is generally considered bad practice to delete table entries, they are usually just marked inactive.