mysql 删除单表内多个字段重复的数据

来源：互联网发布：淘宝dnf代练靠谱吗编辑：程序博客网时间：2024/05/17 22:44

转载地址：http://blog.csdn.net/lingyou37/article/details/17911457

遇到个问题，一个表内两个字段应该加上unique约束，但没加导致出现重复数据，网上找到一条sql可以删掉重复数据，原sql是这样的：

    DELETE from test       WHERE (mid,uid) in       (SELECT mid,uid FROM test GROUP BY mid,uid HAVING COUNT(*)>1)       AND      id NOT in (SELECT id FROM test GROUP BY mid,uid HAVING COUNT(*)>1);

本地会出现这个错误

[Err] 1093 - You can't specify target table 'test' for update in FROM clause http://dev.mysql.com/doc/refman/5.6/en/subquery-errors.html

在子查询的外面嵌一个select from 就可以了

    DELETE from test       WHERE (mid,uid) in       (SELECT mid,uid from (SELECT mid,uid FROM test GROUP BY mid,uid HAVING COUNT(*)>1) s1)       AND      id NOT in (SELECT id from (SELECT id FROM test GROUP BY mid,uid HAVING COUNT(*)>1) s2);

测试表

    CREATE TABLE `test` (        `id` int(11) NOT NULL AUTO_INCREMENT,        `mid` int(11) NOT NULL,        `uid` int(11) NOT NULL,        PRIMARY KEY (`id`)      ) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8

0 0