## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# 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 ...

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required