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 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|
+---------+-----------+---------+1row 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