一、表结构与数据
CREATE TABLE `duptab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `duptab`(`name`) VALUES ('a');
INSERT INTO `duptab`(`name`) VALUES ('a');
INSERT INTO `duptab`(`name`) VALUES ('a');
INSERT INTO `duptab`(`name`) VALUES ('a');
INSERT INTO `duptab`(`name`) VALUES ('b');
INSERT INTO `duptab`(`name`) VALUES ('b');
INSERT INTO `duptab`(`name`) VALUES ('b');
INSERT INTO `duptab`(`name`) VALUES ('b');
INSERT INTO `duptab`(`name`) VALUES ('c');
INSERT INTO `duptab`(`name`) VALUES ('c');
INSERT INTO `duptab`(`name`) VALUES ('c');
二、查询出重复的数据
select name,count(*) from duptab GROUP BY name HAVING count(name)>1;
三、查询出要保留的重复数据
select min(id) ids,name from duptab GROUP BY NAME HAVING COUNT(name)>1;
四、正确写法
delete from duptab
where
name in (select * from (select name from duptab group by name having count(name)>1) a)
and
id not in (select * from (select min(id) from duptab group by name having count(name)>1) b);
五、错误写法
delete from duptab
where
name in (select name from duptab group by name having count(name)>1)
and
id not in (select MIN(id) ids from duptab group by name having count(name)>1)
报错：You can't specify target table 'duptab ' for update in FROM clause。【不能在 from 子句中为 update 指定目标表“duptab ”】不能在同一表中查询的数据作为同一表的更新数据。
