A simple way to remove duplicate data from Mysql _mysql

Copy Code code as follows:

CreateTable ' users ' (
' id ' int (notnullauto_increment),
' Name ' char (notnull),
PRIMARYKEY (' id ')
)

Copy Code code as follows:

Deletefromuserswhereidin (selectmin (ID) fromusersgroupbynamehavingcount (name) >1);

Result error: 1093youcan ' tspecifytargettable ....

The reason is that the MySQL delete action can not carry this table query action, meaning that you delete the users table things can not be based on the information of the users table so this statement will be an error, can not be executed. Just create a temporary table as a query condition. As follows

Copy Code code as follows:

Deletefromuserswhereidin (Select*from (selectmin (ID) fromusersgroupbynamehavingcount (name) >1));

And note that deletefromusers can't use aliases here.

Other methods.

Copy Code code as follows:

Deleteusersasafromusersasa, (selectmin (ID) id,namefromusersgroupbynamehavingcount (name) >1
) asbwherea.name=b.nameanda.id<>b.id;

Create a temporary table:

Copy Code code as follows:

Createtabletmp_usersselectmin (' id '), ' name ' Fromusersgroupbyname;

Truncatetableusers;
Insertintousersselect*fromtmp_users;
Droptabletmp_users;