mysql - 既作为 'DELETE' 的目标又作为单独的数据源

Question

我的 SQL 是:

delete  from user 
where cid in (select  cid  from user group  by  cid   having  count(cid) > 1)
and id not in (select min(id) from  user  group by cid  having count(cid )>1)

提示错误信息:

Table 'user' is specified twice, both as a target for 'DELETE' and as a separate source for data

如何解决这个问题

Answer 1

你尝试过吗:

CREATE TABLE user
    (`cid` int, `username` varchar(50))
;

INSERT INTO user
    (`cid`, `username`)
VALUES
    (1, 'John'),
    (1, 'John'),
    (2, 'Paul'),
    (2, 'Paul'),
    (3, 'Ryan')
;

DELETE FROM user 
 WHERE cid IN
   ( SELECT todel.cid
       FROM (SELECT * FROM user todel
             WHERE todel.cid 
              IN (
                       SELECT t1.cid  
                         FROM user t1
                     GROUP BY t1.cid   
                       HAVING COUNT(t1.cid) > 1
               )
              AND todel.cid 
                NOT IN (
                       SELECT MIN(t2.cid)  
                         FROM user t2
                     GROUP BY t2.cid   
                       HAVING COUNT(t2.cid) > 1
                      )              
            ) as todel
   )

由于对 stackoverflow 的一些研究，我发现了这一点: Delete - I can't specify target table?

听起来这和你遇到的问题是一样的。