mysql 去除重复数据 语句
纠结的过程:
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
参考:
mysql比较作呕的一个delete in操作
http://www.educity.cn/wenda/594988.html
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。