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 ;)
LVL 5
ncomperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
There is an easy way to remove the duplicates and to prevent them to re-appear in future. It is by using a UNIQUE index on the column. There is a MySQL syntax for the index creation that allows to remove any duplicate rows:
    ALTER IGNORE TABLE activefeed ADD UNIQUE INDEX idx_email (email);

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
0
ncomperAuthor Commented:
Error Code: 1062. Duplicate entry 'geoff.hall@allihashhill.co.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
0
chaauCommented:
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?
0
StampelCommented:
Maybe you can try to create a new empty table with the same structure as your original table, then just add unique constrait on your email field. After this, just run :
insert into table_dest (select * from table_src);

the duplicate rows will not be allowed !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.