MySQL Delete duplicate record, save one of the smallest ID

Method 1:1, create a temporary table and select the data you want. 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 setmysql> create temporary table temp as select min (id), name from student group by name; Query OK, 3 rows affectedrecords:3 duplicates:0 warnings:0mysql> truncate TABLE student; Query OK, 0 rows affectedmysql> insert INTO student select * from temp; Query OK, 3 rows affectedrecords:3 duplicates:0 warnings:0mysql> select * FROM student;+----+------+| ID | NAME |+----+------+| 11 | AA | | 13 | bb | | 16 | CC |+----+------+3 rows in setmysql> drop temporary table temp; Query OK, 0 rows affected this method, obviously there is a problem of efficiency. Method 2: Group by name, save the smallest ID to the temporary 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 affectedrecords:3 duplicates:0 warnings:0mysql> Delete from student where ID isn't in (select MiniD f Rom temp); Query OK, 3 rows affectedmysql> 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 statement is as follows:mysql> Delete from student where ID not in (select min (id) fro M student group by name) execution error: 1093-you can ' t specify target table ' student ' for update on FROM clause because the query was used when the data was updated, and the query's The data 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 not in (select MiniD from (select min (id) as MiniD from student group by name) b); Query OK, 3 rows affectedmysql> select * FROM student;+----+------+| ID | NAME |+----+------+| 11 | AA | | 13 | bb | | 16 | CC |+----+------+3 rows in sethttp://www.cnblogs.com/nzbbody/p/4470638.html

MySQL delete duplicate records, save one with the smallest ID