I have a typical setup with a master table that has a related table with many
records per master record. Since MySql does not support sub-selects, how do I
delete just the set of records I want?
TABLE1 - Master
id, name
TABLE2 - Tran
id,mast_id,item,price
Tables are related via (TABLE1.id = TABLE2.mast_id)
I wish I could do a:
DELETE FROM table2 where mast_id
IN (SELECT id FROM table1 WHERE name = 'fred');
If I'm only deleting one master record, then it is easy, one select to find
the master.id then another to delete all table2 records with that mast_id.
But, the problem I'm having is when I want to delete a batch of records from
table1, I also want to get rid of all of the related records so I do not have
any orphans.
- Gary