Delete dupes with Join

I hope someone can help me with this. I'm trying to delete duplicate records from one table by joining another with a condition. For example:

info_table

InfoId, email, firstname, lastname

100, kris1ATyahoo.com, kris, Kot

101, kris1ATyahoo.com, kris, Kot

102, kirs1ATyahoo.com, kris, Kot

103, daveATyahoo.com, dave, Snider

104, daveATyahoo.com, dave, snider

105, tomATyahoo.com, tom, sawyer

106, tomATyahoo.com, tom, sawyer

tran_table

id, infoId, status

1, 100, 1

2, 101, 2

3, 102, 1

4, 103, 2

5, 104, 1

6, 105, 1

7, 106, 1

So, I want to delete from info_table with the below conditions.

I want to delete all the duplicate(by email) from infotable. However, I want to keep those whose status in tran_table is '2'.

Meaning, I want to delete the below:

Among 100,101,102 -- delete all except 101

Among 103,104 -- delete 104

Among 105, 106 -- delete any

Appreciate any help with the query--

Hi,

I don't really see what this has do to with duplicates. As far as I can tell, you simply remove all records with status <> 2, even if that doesn't leave any record for a given email address (like in your third case).

Anyway, check the MySQL manual for the USING clause of the DELETE statement. It allows you to join a table you want to delete from with other auxiliary tables.

Only difference is, I do not want to delete all the rows for 3rd case. I need to keep one of those records.