为什么在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，其原理是什么？

Question

出错的代码（在oracle可执行成功）：
delete from people where name in
(
select name from people GROUP BY name HAVING count(1)>1
) and id not in (select min(id) from people GROUP BY name having count(1)>1 )

修改后的代码：
delete from people where name in (
select a.name from 
(
 select name from people GROUP BY name  HAVING count(1)>1 
)a) and id not in(
SELECT b.id from 
(select min(id) id from people GROUP BY name having count(1)>1) b) 

Answer 1

这个应该是涉及到数据对这个表或者是这一行数据加了锁。你在读的时候对数据加了锁 在删除的时候锁还没有释放所以无法删除成功。
可以先读出记录的索引然后再删 分两步