mysql修改删除You can't specify target table for update in FROM clause的问题

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

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 )

 

posted on 2019-02-21 15:12  淡淡人生过  阅读(542)  评论(0编辑  收藏  举报

导航