mysql去除重复数据遇到1903错误


mysql不能像下面这样使用子查询,

delete from
tablename
WHERE
id IN (SELECT max(id) as id FROM tablename GROUP BY uid,type_id HAVING count(id) > 1)
and id not IN (SELECT min(id) FROM tablename GROUP BY uid,type_id HAVING count(id) > 1);

上面的查询会返回一个错误:[HY000][1093] You can’t specify target table ‘tablename’ for update in FROM clause

查看官方说明:https://dev.mysql.com/doc/refman/5.6/en/update.html

可以改为下面的方式

delete from
tablename
WHERE
id IN (select id from (SELECT max(id) as id FROM tablename GROUP BY uid,type_id HAVING count(id) > 1) as c)
and id not IN (select * from (SELECT min(id) FROM tablename GROUP BY uid,tp e HAVING count(id) > 1) as c);
普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress