delete from table where id not in other table

[bucko]

Hi,

 

I have this situation in mySQL

 

'table1' has an 'id' field.

'table2' has a 'table1_id' field, same value as table1:id

 

I have deleted some entries from 'table1'

Now I want to delete all entries in 'table2' where there is no 'table_id' in 'table1:id'

 

Can someone please tell me  nice way to perform that request?

 

Thanks in advance for any suggestions.

 

bucko.

 

 

[fri3ndly]

Hmm would it be something like

 

DELETE FROM table2 WHERE table_id != 'id'

 

Haven't tested it and dont know if that is valid SQL...give it a try though

[drewbee]

This is psudo code, please backup your database before testing.

 

DELETE FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1)

 

Note: REVERSE LOOKUPS (NOT IN) take a long time to execute if you have a lot of rows.