Method 1:
1. Create a temporary table and select the desired data.
2, clear the original table.
3, temporary table data import into the original table.
4. Delete the temporary table.
Mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | AA |
| 12 | AA |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | CC |
+----+------+
6 Rows in Set
mysql> Create temporary table temp as select min (id), name from student group by name;
Query OK, 3 rows affected
Records:3 duplicates:0 warnings:0
mysql> TRUNCATE TABLE student;
Query OK, 0 rows affected
mysql> INSERT INTO student select * from temp;
Query OK, 3 rows affected
Records:3 duplicates:0 warnings:0
Mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | AA |
| 13 | bb |
| 16 | CC |
+----+------+
3 Rows in Set
mysql> drop temporary table temp;
Query OK, 0 rows affected
This method obviously has the problem of efficiency.
Method 2: Group by name, save the smallest ID to the temp table, delete the record with the ID not in the Minimum ID collection, as follows:
mysql> Create temporary table temp as select min (id) as MiniD from student group by name;
Query OK, 3 rows affected
Records:3 duplicates:0 warnings:0
mysql> Delete from student where ID not in (select MiniD from temp);
Query OK, 3 rows affected
Mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | AA |
| 13 | bb |
| 16 | CC |
+----+------+
3 Rows in Set
Method 3: Operate directly on the original table, the easy-to-think SQL statements are as follows:
mysql> Delete from student where the ID is not in (the Select min (id) from the student group by name);
Execution error: 1093-you can ' t specify target table ' student ' for update in FROM clause
The reason is that the query is used when updating the data, and the data of the query is updated again, and MySQL does not support this approach.
How to circumvent this problem?
Add another layer of encapsulation, as follows:
mysql> Delete from student where ID is not in (select MiniD from (select min (id) as MiniD from student group by name) b);
Query OK, 3 rows affected
Mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | AA |
| 13 | bb |
| 16 | CC |
+----+------+
3 Rows in Set
MySQL Delete duplicate record, save one of the smallest ID