删除重复数据只保留一条数据

一、表结构与数据

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 ”】不能在同一表中查询的数据作为同一表的更新数据。