Easy MySQL Multi-Column Duplicate Search

By Michael Kramer on October 3rd 2014 @ 9:40 am

I had the need to find duplicates across multiple columns easily, and I read through several sources, most of them used JOIN statements on themselves, or temporary tables, but I finally found a solution that is easy, simply by grouping by the fields, and doing a HAVING COUNT(*) > 1

Example:

SELECT GROUP_CONCAT(`row_id`) AS `ids`, COUNT(*) FROM `table` GROUP BY `column_a`, `column_b`, `column_c` HAVING COUNT(*) > 1

Using PHP I was able to easily loop through them and delete the duplicates. I would just take the ids, explode them, skip the first one, and delete the others by doing a:

DELETE FROM `table` WHERE `row_id` IN($id_list);
comments powered by Disqus

Similar

Become an Author