MYSQL删除重复的记录

注意：mysql不支持删除数据的同时查询

例子：

DELETE from test2 where name in 
(select  name from test2  group by name  having count(name) > 1) 
and id not in 
(select min(id) from test2 group by name  having count(name)>1)

报错

[Err] 1093 - You can't specify target table 'test2' for update in FROM clause

表结构

正确的删除重复数据sql

DELETE FROM test2 WHERE id IN (
SELECT * from (
select id from test2 where name in (
select  name from test2  group by name  having count(name) > 1
) 
and id not in (
select min(id) from  test2  group by name  having count(name)>1
)
)a);

效果：