表中出现重复数据，需要删除重复数据，只保留一条

DELETE FROM    crm_participant WHERE    id IN ( SELECT    c.id cid FROM    crm_participant c WHERE    c.parentPhone IN ( SELECT a.parentPhone FROM crm_participant a GROUP BY a.parentPhone HAVING count( a.parentPhone ) > 1 )     AND c.id NOT IN ( SELECT min( b.id ) FROM crm_participant b GROUP BY b.parentPhone HAVING count( b.parentPhone ) > 1 ) ORDER BY    c.parentPhone     ) 错误信息：

> 1093 - You can't specify target table 'crm_participant' for update in FROM clause

> 时间: 0.005s

　　

问题分细：       mysql不允许对同一个表中查出来的数据作为条件，在执行跟新操作。 在一条 sql 语句中不能先查出来部分内容，再同时又对当前表作修改。

 

解决办法：这个是正确的sql,其实就是对上边的红色部分的查询sql进行了一层包裹。让查询出来的信息被一个  select 包裹一下，然后作为条件就可以了 DELETE FROM    crm_participant WHERE    id IN (SELECT    v.cid FROM    (SELECT    c.id cid FROM    crm_participant c WHERE    c.parentPhone IN ( SELECT a.parentPhone FROM crm_participant a GROUP BY a.parentPhone HAVING count( a.parentPhone ) > 1 )     AND c.id NOT IN ( SELECT min( b.id ) FROM crm_participant b GROUP BY b.parentPhone HAVING count( b.parentPhone ) > 1 ) ORDER BY    c.parentPhone     ) v      )