Delete rows from table based column values found in other table
Posted on 2008-10-04
I have a working web application with multiple tables. I need to write a small PHP script that when it runs, deletes records from a table if the value of its first column is NOT found in a second table.
Let me explain.
I have two tables that this PHP script needs to compare, call them table1 and table2. In each table there is a column called mid.
I need a script that will delete a record in table1 if the value of the mid in table1 is not in table2, but if the value of the mid in table1 is in table2, the record is left untouched.
The values of mid is always numerical, such as 12345.
So the PHP script needs to look at all records in table1 (which may be one record or 1000 of records. Note that after this PHP script runs, the remaining records are copied to another table and table1 is emptied).
Note that table2 does not have any duplicate values for mid. So in table2, a value for mid is either present once or never.
So I need a PHP script that says this is the value of the mid in table1, if it is found in the table2 mid column, then the record in table1 stays. If the value of the mid in table1 is NOT found in the mid column in table2, then the row in table1 is deleted.