Find duplicate field values in MySQL

I recently had to clean up a database table of user data because it had the same email addresses assigned to multiple users. The first step was putting together a query that pulled only the duplicate email addresses and also told me how many times they occurred – from there the actual clean up process was fairly straight forward.

This simple query looked something like this:

I find this to be a particularly helpful SQL snippet as this kind of clean up process is fairly common. It uses MySQL’s HAVING clause because WHERE cannot handle aggregate functions – COUNT() in this case.

2 thoughts on “Find duplicate field values in MySQL

  1. Thanks for the snippet … but how can I get all records returned so I can compare the rows. In my case, duplicate records need to be compared manually, so I need more than just the count. Can you help with that?


  2. @dcolumbus, I have a suggestion. Sometimes the easiest thing to do is just export the table to Excel and then use Excel’s more intuitive tools to highlight the duplicate rows/values.


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s