Delete related records after a search

[pgilmour]

I'm trying to delete records in a related table in the following way:

Find every record in table1 whose email address is "blah@blah.com"

Delete every record in table2 that has a matching ID (they're related via ID) to the IDs of the found set in table1.

I'm using MySQL 5 on Linux.

TIA for any help!

[chazzy]

delete from table2 where table2.id in (select table1.id where email = 'the email address')

[pgilmour]

Chazzy, thanks so much for the reply.

I ran your example through the SQL prompt in PHPMyAdmin and it nearly ran first time! Not sure why, but I had to make a small tweak:

delete from `table2` where `theFKId` in
(SELECT `theID` FROM `table1` WHERE `email` LIKE CONVERT( _utf8 %myemail@email.com%'
USING latin1 ))


Many thanks again!