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

时间：2022-03-17 14:27:36

遇到个问题，一个表内两个字段应该加上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








标签：

相关文章