Handling Duplicates - MySQL - page 561

Solution
Select the unique rows from the table into a second table, then use it to replace the
original one. Or use DELETE … LIMIT n to remove all but one instance of a specific set
of duplicate rows.
Discussion
Recipe 16.1 discusses how to prevent duplicates from being added to a table by creating
it with a unique index. However, if you forget to include the index when you create a
table, you may discover later that it contains duplicates and that it's necessary to apply
some sort of duplicate-removal technique. The catalog_list table used earlier is an
example of this because it contains several instances in which the same person appears
multiple times:
mysql> SELECT * FROM catalog_list ORDER BY last_name, first_name;
+-----------+-------------+--------------------------+
| last_name | first_name | street |
+-----------+-------------+--------------------------+
| Baxter | Wallace | 57 3rd Ave. |
| BAXTER | WALLACE | 57 3rd Ave. |
| Baxter | Wallace | 57 3rd Ave., Apt 102 |
| Brown | Bartholomew | 432 River Run |
| Isaacson | Jim | 515 Fordam St., Apt. 917 |
| McTavish | Taylor | 432 River Run |
| Pinter | Marlene | 9 Sunset Trail |
| Pinter | Marlene | 9 Sunset Trail |
+-----------+-------------+--------------------------+
To eliminate duplicates, you have a few options:
• Select the table's unique rows into another table, then use that table to replace the
original one. This works when “duplicate” means “the entire row is the same as
another.”
• To remove duplicates for a specific set of duplicate rows, use DELETE … LIMIT n to
remove all but one row.
This recipe discusses each duplicate-removal method. When you consider which to
choose under various circumstances, the applicability of a given method to a specific
problem is often determined by several factors:
• Does the method require the table to have a unique index?
• If the columns in which duplicate values occur may contain NULL , will the method
remove duplicate NULL values?
• Does the method prevent duplicates from occurring in the future?