1

my SQL is:

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)

Prompt error message：

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

How to fix this

hassan
  • 7,812
  • 2
  • 25
  • 36
yjcylyetz
  • 11
  • 1
  • 2

1 Answers1

3

did you try:

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
   )

Due to some research on stackoverflow i found this: Delete - I can't specify target table?

sounds like it is the same problem you have.

Esteban P.
  • 2,789
  • 2
  • 27
  • 43
  • btw: i highly recommend everybody to format SQL statements properly whenever i see somebody in my team writing code without proper shifting, he rans into a serious discussion with me – Esteban P. Jun 21 '17 at 09:54
  • 1
    what is the 'proper' way to format SQL statements though... I see combinations of tabs and spaces in your SQL that wouldn't fly with my guys ;-) Wholeheartedly agree with generally formatting it in a readable way though... not just one-lining it or throwing it all in left aligned... – Gallus Jun 21 '17 at 10:03
  • @Gallus: well, as you pointed out. the important thing is, that it should be readable. should be easy to see where a bracket opens and closes. group lines which belong to the same select, use same shifting for those, and so on. unfortunately most people are too lazy for that – Esteban P. Jun 21 '17 at 10:08
  • @yjcylyetz i updated my statement and also added a link. also have a look at the link. might be the same problem you have – Esteban P. Jun 22 '17 at 07:15
  • @yjcylyetz but the statement won't delete anything in my opinion. the second statement with the NOT IN ... MIN ...having COUNT() > 1 will return the same IDs like the IN ...COUNT() > 1 statement or am i completely wrong? – Esteban P. Jun 22 '17 at 07:24