MySQL Database super Strong delete duplicate data statement

The problem with students today is that this table has MD this field to repeat. See how to deal with it.

The code is as follows	Copy Code
SELECT * FROM Students Where MD in (select MD from Students GROUP by MD have COUNT (MD) > 1) ORDER by MD

Note that this group of fields, please index, otherwise very slow

The code is as follows	Copy Code
Delete from students Where MD in (select MD from Students GROUP by MD have COUNT (MD) > 1) and ID not in (select min (ID) from students GROUP by MD have COUNT (MD) >1)

This statement will be an error under MySQL

#1093 –you can ' t specify target table ' students ' for update in FROM clause

The reason is as if MySQL does not allow us to make joint deletes the conditional statement points to its own table.

The policy is to use temporary tables to store the IDs that are to be deleted

The code is as follows	Copy Code
CREATE TABLE tmp (ID int); INSERT into TMP (ID) Select ID from students Where MD in (select MD from Students GROUP by MD have COUNT (MD) > 1) and ID not in (the Select min (ID) from students GROUP by MD have COUNT (MD) >1); Delete from students where ID in (SELECT ID from TMP);

Get the data that will be deleted

The code is as follows	Copy Code
SELECT * FROM Students Where MD in (select MD from Students GROUP by MD have COUNT (MD) > 1) and ID not in (select min (ID) from students GROUP by MD have COUNT (MD) >1)

The filtered data is obtained without deleting the data. If not removed, this SQL statement can be used to display unique data

/tbody>

code is as follows	copy code
Select * From students where ID in (select min (ID) from students GROUP by MD has COUNT (MD) >1)