Solved

MYSQL remove duplicates

Posted on 2014-10-09
4
251 Views
Last Modified: 2014-10-26
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 ;)
0
Comment
Question by:ncomper
  • 2
4 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40372011
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
 
LVL 5

Author Comment

by:ncomper
ID: 40372386
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
 
LVL 24

Expert Comment

by:chaau
ID: 40372395
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
 
LVL 7

Accepted Solution

by:
Stampel earned 500 total points
ID: 40372495
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now