2 tables whats deleted?

I have 2 tables in mysql.

When something was inserted into table1, it was also inserted into table2. In this way, the auto increment in the id columns of both tables always alligned.

Then I deleted many of the rows which fit a certain criteria from table1, but didn't delete any rows from table2. I would now like to delete from table 2 all those rows I have deleted from table1.

So basically, I want to delete all rows from Table2 where id does not have exist in table1开发者_JS百科...and keep the rows where the id column does exist in both table1 and table2...

How can I do this as cleanly and simply as possible?

I can use php or mysql/phpmyadmin...

---

When something was inserted into table1, it was also inserted into table2. In this way, the auto increment in the id columns of both tables always alligned.

Please don't do that. Auto Increment is not something to rely on to keep values in sync. You should do the following:

1. Insert data in table 1, with auto_increment;
2. Get Id of last inserted row;
3. Insert data in table 2, without auto_increment, with id = table1.id

I warmly suggest you refactor asap, or you'll have thousands of similar problems in the future.

(posting this as an answer since anyone with the same problem of OP is a likely victim of the same architectural mistake).

---

If the two tables are related then you are better off using CASCADE DELETE

otherwise

Delete
FROM table2
WHERE ID not in (select t1.ID from table1 t1);

---

You can delete with the joins but take the backup first

 DELETE table2 FROM table2 LEFT JOIN table1 
 ON table2.id=table1.id 
 WHERE table1.id IS NULL;

---

To delete records from both tables at the same time:

DELETE `table1`, `table2`
FROM `table1`
LEFT OUTER JOIN `table2` ON `table1`.`id` = `table2`.`id`
WHERE @condition

That way you do not need to send queries for each table to the database.

---

Here is the most compact solution:

delete table2 from table2 left join table1
              using (id)
              where table1.id is null

EDIT: Not just compact. Compact and efficient. The subquery solution is going to eat your CPU time like a starving tiger.

---

$result = mysql_query(SELECT ID FROM table2 WHERE table2.ID NOT IN ( SELECT table1.ID FROM table1 WHERE table2.ID=table1.ID ));
$rows = mysql_num_rows($result);
$i = 0;
while($i < $rows){
    $cleanup = mysql_query(DELETE from table2 WHERE ID='$result[$i] ));
}