How to Handle Duplicate Records by Deleting and Leaving Only One Record from Each Duplicate Records in MySQL Database

Have you ever got difficulty when handling the duplicate records in a table in MySQL database, where you want to delete the records and pretty leaves one record from each duplicate records? This usually happens in the table who did not have the primary key, so that it can cause to store the duplicate records. The duplicate records are usually obtained from the data import process that was done repeatedly. Well, the following trick you can use to handle such cases. Quite easy and a little bit outsmart.

The first step, you must copy the table structure that stores the duplicate records to another table name (copy table structure only, not including records). The second step, simply copy only one or distinct record from each duplicate records using a single MySQL statement to the copied/destination table. Lastly, the third step, check the results in the destination table. If everything goes smoothly, then delete the original table, and rename back the copied/destination table to the original table name. Curious?

For example, you have a table named table1 which has the structure like this:

mysql> DESCRIBE `table1`;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| code        | char(2)     | YES  |     |         |       |
| description | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

and it has the duplicate records as following:

mysql> select * from table1;
+------+-------------+
| code | description |
+------+-------------+
| 1    | One         |
| 1    | One         |
| 2    | Two         |
| 2    | Two         |
| 3    | Three       |
| 3    | Three       |
| 3    | Three       |
| 4    | Four        |
| 4    | Four        |
| 4    | Four        |
| 4    | Four        |
| 5    | Five        |
| 5    | Five        |
| 5    | Five        |
| 5    | Five        |
| 5    | Five        |
+------+-------------+

Let’s say, you want to delete and leave only one record from each the duplicate records. So the results become like this:

+------+-------------+
| code | description |
+------+-------------+
| 1    | One         |
| 2    | Two         |
| 3    | Three       |
| 4    | Four        |
| 5    | Five        |
+------+-------------+

So, in order to get that results, copy only the table structure of table1 to (for example) table1_copy using the following SQL statement:

mysql> CREATE TABLE `table1_copy` LIKE `table1`;

Afterwards, copy or insert only one record of the duplicate records from table1 into the table1_copy using the following SQL statement:

INSERT INTO table1_copy
SELECT DISTINCT * FROM table1;

Voila… here is the result:

mysql> SELECT * FROM table1_copy;
+------+-------------+
| code | description |
+------+-------------+
| 1    | One         |
| 2    | Two         |
| 3    | Three       |
| 4    | Four        |
| 5    | Five        |
+------+-------------+

Ups, don’t forget to remove the table1 table (assuming all the steps above goes smoothly):

DROP TABLE IF EXISTS `table1`;

and then rename the destination table to the original one:

RENAME TABLE `table1_copy` TO `table1`;

It’s very easy, isn’t? :)

Comments

  1. pankaj says

    Is it possible that there is no need to generate the copy of table and we can reduce the redundency on a single table.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>