Saturday, September 19, 2015

Remove duplicate record in table

In this post, we are going to see how to remove duplicate records from table.

Create a test table
mysql> CREATE TABLE test_table (`id` int(11) DEFAULT NULL,`name` varchar(10) DEFAULT NULL);
Insert duplicate values in test table.
mysql> insert into test_table values (1,'aaa'),(1,'aaa'),(1,'aaa'),(1,'bbb'),(1,'bbb'),(1,'bbb');
mysql> select * from test_table;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
| 1 | aaa |
| 1 | bbb |
| 1 | bbb |
| 1 | bbb |
+------+------+
Remove duplicate rows from test table
mysql> alter IGNORE table test_table add UNIQUE key (id,name);
Records: 6 Duplicates: 4 Warnings: 0
mysql> select * from test_table ;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | bbb |
+------+------+

1 comments: