MySQL Cookbook

Eliminating Duplicates from a Table

Problem

You want to remove duplicate records from a table so that it contains only unique rows.

Solution

Select the unique rows from the table into a second table that you use to replace the original one. Or add a unique index to the table using ALTER TABLE, which will remove duplicates as it builds the index. Or use DELETE ... LIMIT n to remove all but one instance of a specific set of duplicate rows.

Discussion

If you forget to create a table with a unique index to prevent the occurrence of duplicates within the table, you may discover later that it’s necessary to apply some sort of duplicate-removal technique. The cat_mailing table used in earlier sections is an example of this, because it contains several instances where the same person is listed multiple times.

mysql> SELECT * FROM cat_mailing 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           |
+-----------+-------------+--------------------------+

The table contains redundant entries and it would be a good idea to remove them, to eliminate duplicate mailings and ...