mysql> select * from role group by role_name,deleted;
+---------+-----------+---------+
| role_id | role_name | deleted |
+---------+-----------+---------+
| 2       | xue       | 12      |
| 1       | zhao      | 12      |
| 3       | zhao      | 13      |
+---------+-----------+---------+
3 rows in set
mysql>  delete from role c where c.role_id not in (select b.role_id from role c group by role_name,deleted);
1064 - You have 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 1

mysql>  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      |
+---------+-----------+---------+
1 row in set


mysql>  delete from role c where c.role_id not in (select b.role_id from role b  group by role_name,deleted);
1064 - You have 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‘ at line 1

mysql>  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 update in FROM clause

mysql>  delete from role  where role_id not in (select role_id from role   group by role_name,deleted);
1093 - You can‘t specify target table ‘role‘ for update in FROM clause

mysql>  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 update in FROM clause
mysql>  delete from role  where role_id not in (select t.role_id from role t  group by role_name,deleted);
1093 - You can‘t specify target table ‘role‘ for update in FROM clause
mysql>  delete from role  where role_id not in (select t.role_id from role t  group by t.role_name,t.deleted);
1093 - You can‘t specify target table ‘role‘ for update in FROM clause
mysql>  delete from role  where role_id NOT IN (select t.role_id from role t  group by role_name,deleted);
1093 - You can‘t specify target table ‘role‘ for update in FROM clause
mysql>  delete from role  where role_id NOT IN select *  from ( (select t.role_id from role t  group by role_name,deleted));
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select *  from ( (select t.role_id from role t  group by role_name,deleted))‘ at line 1
mysql>  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 have its own alias

mysql>  delete from role  where role_id NOT IN ( select *  from  ( select t.role_id from role t  group by t.role_name,t.deleted));
1248 - Every derived table must have its own alias

mysql>  delete from role  where role_id NOT IN ( select *  from  ( select t.role_id from role t  group by t.role_name,t.deleted) t1);
Query OK, 1 row affected