ncomper
asked on
MYSQL remove duplicates
Hi All I know this is really simple but im a bit of a dunse with MYSQL so wanted a bit of advice, I have a table "Activefeed" which has a coumn in it "email". I would like to remove all of the rows which have a duplicate email addresss, i.e if there are 2 rows with the same email address delete on of the rows and so on. I thought this would of been easy but there is a lot of different options which creating and removing rows etc, all I want to end up with is my table "activefeed" to effectively be cleaned and only have 1 row per email address and all duplicates deleted.
Apologies but in your answer can you treat me like an idiot ;)
Apologies but in your answer can you treat me like an idiot ;)
ASKER
Error Code: 1062. Duplicate entry 'geoff.hall@allihashhill.c o.uk' for key 'idx_email'
Hi i get this error when I run it, thank you, Although if possible I would like to know a way that it can run using delete / distinct commands if possible (have also seen code where you create a new temp table
Hi i get this error when I run it, thank you, Although if possible I would like to know a way that it can run using delete / distinct commands if possible (have also seen code where you create a new temp table
It is very strange. According to this document (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html) the IGNORE keyword must automatically remove duplicates. What version of MySQL are you using?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
By using IGNORE keyword you instruct MySQL to remove duplicates. However, there is no control on what record is chosen by MySQL. For example, there are emails attached to different accounts. You may know that email is really for account B rather than A, because account A has not used your system for a few years now. MySQL does not know this, and it can delete the email for account B instead. If you have ideas what records to keep, share them with us and we can help you