How to remove all duplicate records in MySQL table?

To delete duplicate records from a table, we can use the DELETE command. Now let's create a table.

mysql> create table DuplicateDeleteDemo
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.60 sec)

Insert records into the table "DuplicateDeleteDemo": Here, we add "John" as a duplicate record 3 times.

mysql> insert into DuplicateDeleteDemo values(1,'John');
Query OK, 1 row affected (0.11 sec)

mysql>  insert into DuplicateDeleteDemo values(1,'John');
Query OK, 1 row affected (0.14 sec)

mysql>  insert into DuplicateDeleteDemo values(2,'Johnson');
Query OK, 1 row affected (0.13 sec)

mysql>  insert into DuplicateDeleteDemo values(1,'John');
Query OK, 1 row affected (0.14 sec)

To display all records, use the SELECT statement.

mysql> select *from DuplicateDeleteDemo;

The following is the output containing duplicate records.

+------+---------+
| id   | name    |
+------+---------+
|    1 | John    |
|    1 | John    |
|    2 | Johnson |
|    1 | John    |
+------+---------+
4 rows in set (0.00 sec)

In the above output, there are 4 records in the table, 3 of which are duplicates.

To delete duplicate records, use DELETE.

mysql> delete from DuplicateDeleteDemo where id=1;
Query OK, 3 rows affected (0.19 sec)

To check if a record has been deleted, let's display all records again.

mysql> select *from DuplicateDeleteDemo;

The following output shows that all duplicate records have been removed.

+------+---------+
| id   | name    |
+------+---------+
|    2 | Johnson |
+------+---------+
1 row in set (0.00 sec)

The above is the detailed content of How to remove all duplicate records in MySQL table?. For more information, please follow other related articles on the PHP Chinese website!