MySQL Delete duplicate data only keep one bar

Interview encounter a MySQL interesting topic, how to remove duplicate name row from student table, and keep record of minimum ID?

Unfortunately didn't do it at that time, home search, found that the use of sub-query can be quickly resolved.

1, delete redundant records in the table, duplicate records is username judgment, leaving only the record with the smallest ID

Delete  fromStudentwhereusernameinch(SelectUsername fromStudentgroup byUsername having Count(username)>1) andId not inch(Select min(ID) asId fromStudentgroup byUsername having Count(username)>1)

(The above statement is executed in MySQL with an error:

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, andMySQL does not support this approach. Both Oracel and msserver support this approach.

How to circumvent this problem?

Plus a layer of encapsulation,

Delete  fromStudentwhereusernameinch(SelectUsername from(SelectUsername fromStudentGroup  byUsername having Count(username)>1) a) andId not inch(SelectId from(Select min(ID) asId fromStudentGroup  byUsername having Count(username)>1) b)

Note Select min (ID) is followed by an as ID.

There are more simple ways to do this (for a single field):

Delete  from where   not in ( Select from (selectmin as from (group by username) b);

Expand:

2.Delete extra duplicate records (multiple fields) in the table, leaving only the record with the smallest ID

Delete  fromStudent Awhere(A.USERNAME,A.SEQ)inch(SelectUsername,seq from(SelectUsername,seq fromAGroup  byUsername,seq having Count(*)> 1) t1) andId not inch(SelectId from(Select min(ID) fromVitaeGroup  byUsername,seq having Count(*)>1) T2)

Reference article:

6407280

MySQL Delete duplicate data only keep one bar