MySQL Remove duplicate data statement

The tangled process:

Mysql> SELECT * from role GROUP BY role_name,deleted;+---------+-----------+---------+| role_id | role_name | deleted |+---------+-----------+---------+|2 | Xue | 12      || 1 | Zhao | 12      || 3 | Zhao | 13      |+---------+-----------+---------+3rows in setmysql> delete from role C where c.role_id not in (select b.role_id from role C GROUP by Role_name, deleted);1064-you has an error in your SQL syntax;Check the manual that corresponds to your MySQL server version for the right syntax to use near ' where c.role_id not ' In (select b.role_id from role C Group by role_name,deleted) ' at line 1mysql> SELECT * from role C where c.role_id not in (select b.role_id from role B group by Role_name, deleted);+---------+-----------+---------+| role_id | role_name | deleted |+---------+-----------+---------+|4 | Xue | 12      |+---------+-----------+---------+1row in setmysql> delete from role C where c.role_id not in (select b.role_id from role B group by Role_name, deleted);1064-you has an error in your SQL syntax;Check the manual that corresponds to your MySQL server version for the right syntax to use near ' where c.role_id not ' In (select b.role_id from role B group by role_name,deleted ' on line 1mysql> Delete from role where role_id not in (select b.role_id from role B group by Role_name, deleted);1093-You can ' t specify target table ' role ' for the update in the from clausemysql> delete from role where role_id not in (s Elect role_id from role group by Role_name, deleted);1093-You can ' t specify target table ' role ' for the update in the from clausemysql> delete from role where not role_id in ( Select role_id from role GROUP by Role_name, deleted);1093-You can ' t specify target table ' role ' for the update in the from clausemysql> delete from role where role_id not in (s Elect t.role_id from role T GROUP by Role_name, deleted);1093-You can ' t specify target table ' role ' for the update in the from clausemysql> delete from role where role_id not in (s Elect t.role_id from role T GROUP by T.role_name, t.deleted);1093-You can ' t specify target table ' role ' for the update in the from clausemysql> delete from role where role_id not in (s Elect t.role_id from role T GROUP by Role_name, deleted);1093-You can ' t specify target table ' role ' for the update in from clausemysql> delete from role where role_id not in SE Lect * FROM ((select t.role_id from role T GROUP by Role_name, deleted));1064-you has an error in your SQL syntax;Check the manual, corresponds to your MySQL server version for the right syntax to use near ' select * FROM ((SE Lect t.role_id from role T Group by role_name,deleted) " at line 1Mysql> Delete from role where role_id not in (SELECT * from(select t.role_id from role T Group by role_name,deleted));1248 - every derived table must has its own aliasmysql> Delete from role where role_id not in (SE Lect * FROM (select t.role_id from role T GROUP by T.role_name, t.deleted));1248- Every derived table must has its own alias 

Finally made it through:

mysql>  Delete from role  where role_id isn't in (SELECT *  from  (select t.role_id from role T  Group B Y t.role_name,t.deleted) t1); Query OK, 1 row affected

Reference:
MySQL Compare gagging a delete in operation
Http://www.educity.cn/wenda/594988.html