---

Delete the duplicate records in MySQL by various ways, you will learn some intrested queries

1- Delete the duplicate records using JOIN

The following query deletes the duplicate records and keep the row with the lowest id :

DELETE t1 from table_name t1 , table_name t2 where t1.`id` > t2.id AND t1.`name`=t2.`name`;

The following query deletes the duplicate records and keep the row with the highest id :

DELETE t1 from table_name t1 , table_name t2 where t1.`id` < t2.id AND t1.`name`=t2.`name`;

2- Delete the duplicate records using following solution

Step 1: copy the table structure into another temp table

CREATE TABLE temp LIKE table_name;

Step 2: copy the table data into temp table with unique records using DISTINCT

INSERT INTO temp SELECT DISTINCT * FROM table_name;

Step 3: Drop the original table

DROP TABLE table_name;

Step 3: Rename temp table into original table

RENAME TABLE temp TO table_name;

---

Related

find the nth highest salary in mysql without limit
How to find greater than average salary records in MySQL
how to find duplicate records in MySQL query
How to use second index forcefully in MySQL
How to get total number of rows in MySQL table with and without count