Menu
I have a table with columns for ID, firstname, lastname, address, email and so on.
Is there any way to delete duplicate email addresses from the TABLE?
Additional information (from comments):
If there are two rows with the same email address one would have a normal firstname and lastname but the other would have 'Instant' in the firstname. Therefore I can distinguish between them. I just want to delete the one with first name 'instant'.
Note, some records where the firstname='Instant' will have just 1 email address. I don't want to delete just one unique email address, so I can't just delete everything where firstname='Instant'.
Please help me out.
724
Approach: Using DELETE and WHERE clause [Accepted] Email = p2. Email ; Then we need to find the bigger id having same email address with other records. So we can add a new condition to the WHERE clause like this.
You can't directly do that in MySQL because there is no function to urlencode or urldecode strings. You will have to create a User Defined Function to handle that process. Once you have that function just go for a simple group by with a having clause.
Holding the Ctrl key, select duplicate emails one by one manually; and then press the Delete key to remove them.
DELETE n1 FROM customers n1, customers n2 WHERE n1.ID > n2.ID AND n1.email = n2.email
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
