A Simple Method for MYSQL to delete duplicate data

Copy codeThe Code is as follows:
CREATETABLE 'users '(
'Id' int (10) NOTNULLAUTO_INCREMENT,
'Name' char (50) NOTNULL,
PRIMARYKEY ('id ')
)

Copy codeThe Code is as follows:
Deletefromuserswhereidin (selectmin (id) fromusersgroupbynamehavingcount (name)> 1 );

Error: 1093youcan tspecifytargettable ....

The reason is that the mysql Delete action cannot carry the query action of the current table. This means that you cannot delete the users table based on the users table information. Therefore, this statement reports an error and cannot be executed. You only need to create a temporary table as the query condition. As follows:

Copy codeThe Code is as follows:
Deletefromuserswhereidin (select * from (selectmin (id) fromusersgroupbynamehavingcount (name)> 1 ));

Also note that efromusers cannot use aliases here.

Other methods.
Copy codeThe Code is as follows:
Deleteusersasafromusersasa, (selectmin (id) id, namefromusersgroupbynamehavingcount (name)> 1
) Asbwherea. name = B. nameanda. id <> B. id;

Create a temporary table:
Copy codeThe Code is as follows:
Createtabletmp_usersselectmin ('id'), 'name' fromusersgroupbyname;

Truncatetableusers;
Insertintousersselect * fromtmp_users;
Droptabletmp_users;