mysql删除重复数据只保留id最大一条记录 | 您所在的位置:网站首页 › mysql中删除重复数据只保留一条记录吗 › mysql删除重复数据只保留id最大一条记录 |
目的: 一张表,表名 credit_user 相同的 user_code有多条重复数据,现在只保留一条数据 一:首先是这么想的 DELETEFROM credit_userWHERE user_code IN ( SELECT user_code FROM credit_user GROUP BY user_code HAVING count(user_code) > 1 ) and id NOT IN ( SELECT min(id) FROM credit_user GROUP BY user_code HAVING count(user_code) > 1 );
发现在mysql中会报错 错误信息:[Err] 1093 - You can't specify target table 'credit_user' for update in FROM clause 于是查资料说是不能先select出同一表中的某些值,再update这个表(在同一语句中) 二:正确写法 DELETEFROM credit.credit_userWHERE user_code IN ( SELECT * FROM ( SELECT user_code FROM credit_user GROUP BY user_code HAVING count(user_code) > 1 ) b ) and id NOT IN ( SELECT * FROM ( SELECT min(id) FROM credit_user GROUP BY user_code HAVING count(user_code) > 1 ) c );
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql |
CopyRight 2018-2019 实验室设备网 版权所有 |