首页 > 代码库 > mysql 去除重复数据 语句
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 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 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 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 |+---------+-----------+---------+1 row 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 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 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 update in FROM clausemysql> 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 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 update in FROM clausemysql> 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 clausemysql> 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 clausemysql> 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 clausemysql> 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 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 have its own aliasmysql> 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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。