January 25th, 2013, 01:19 PM
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:
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
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--
January 25th, 2013, 05:02 PM
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.
January 26th, 2013, 06:59 AM
Only difference is, I do not want to delete all the rows for 3rd case. I need to keep one of those records.
Originally Posted by Jacques1