106

This code doesn't work for MySQL 5.0, how to re-write it to make it work

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 ))

I want to delete columns that dont have unique id. I will add that most of the time its only one id(I tried the in syntax and it doesnt work as well).

IAdapter
  • 62,595
  • 73
  • 179
  • 242

4 Answers4

257

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause.

Additionally, as shown in this answer you cannot modify the same table from a subquery within the same query. However, you can either SELECT then DELETE in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

Or use joins as suggested by Mchl.

Community
  • 1
  • 1
BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
  • 2
    I had a table with 150 duplicate keys. I executed the above query and it said "144 rows affected", but there where still duplicate keys. So I executed the query again and it says 5 rows affected, again: 1 row affected. Then all the duplicate keys where gone. Why is this? – Alex May 24 '12 at 07:17
  • 1
    This is happening, because your are only deleting 1 entry out of each set of duplicates: `SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )` – havvg Oct 10 '13 at 13:57
  • #1248 - Every derived table must have its own alias – thang Jun 01 '15 at 05:25
  • @thang: Which is why I said to alias the inner subquery. – BoltClock Jun 01 '15 at 05:34
  • No, still doesn't work. If you don't alias the inner subquery, it gives you a different error message. I ended up creating a temporary table, dumping everything to it, and then delete where id in that temp table. – thang Jun 01 '15 at 05:41
  • @thang: Post a separate question. As it is there is nothing I can do if all you're going to give me is an error message and "doesn't work". – BoltClock Jun 01 '15 at 05:43
  • @thang: I tried it four years ago and just tried it again now, and it works. – BoltClock Jun 01 '15 at 05:45
  • I did the exact same thing. Did you use innodb? I wonder if it depends on the engine. – thang Jun 01 '15 at 05:45
  • @thang: I tested it on both MyISAM and InnoDB and it works. What is your version of MySQL? I highly recommend posting a separate question so you can provide more details. – BoltClock Jun 01 '15 at 05:48
  • let me dig into it some more. probably something stupid i am doing. if it still doesn't work, i'll post a new question. thanks! – thang Jun 01 '15 at 05:50
  • 1
    Will you please explain what does the "As p" does? – Cricketer Aug 18 '15 at 20:02
  • @Samarth it is an alias for the derived table (SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )) – daisura99 May 25 '17 at 07:40
27
DELETE 
  p1
  FROM posts AS p1 
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • This appears to work, but I'm confused by the syntax and can't find any resources elsewhere to explain it. `CROSS JOIN` apparently performs a cartesian join, so seems like this might do unnecessary work, or perform sub-optimally? Could anyone explain? – wintron Mar 19 '14 at 14:12
  • It will do a cartesian product only if there's no `USING` clause. With `USING` the product is limited to pairs having same value in `id` column, so it is in fact very limited. – Mchl Mar 19 '14 at 21:54
  • Could you do the same thing with inner join? I.E. `DELETE p1 FROM posts AS p1 INNER JOIN ( SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1 ) AS p2 ON p2.ID=p1.ID` – Cave Johnson Apr 14 '16 at 18:48
  • 2
    @Andrew: Yes. Functionally these joins are exactly the same. – Mchl Apr 15 '16 at 05:52
  • @wintron `CROSS JOIN` is the same as `[INNER] JOIN` in MySQL; it doesn't implement `CROSS JOIN` in the way that ANSI SQL describes it. `SELECT ... FROM a JOIN b USING (x)` is syntactic sugar for `SELECT ... FROM a JOIN b ON b.x = a.x`. – Jivan Pal May 06 '22 at 14:09
13

you can use inner join : 

DELETE 
    ps 
FROM 
    posts ps INNER JOIN 
         (SELECT 
           distinct id 
         FROM 
             posts 
         GROUP BY id  
      HAVING COUNT(id) > 1 ) dubids on dubids.id = ps.id
Charif DZ
  • 14,415
  • 3
  • 21
  • 40
0

If you want to delete all duplicates, but one out of each set of duplicates, this is one solution:

DELETE posts
FROM posts
LEFT JOIN (
    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) = 1

    UNION

    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) != 1
) AS duplicate USING (id)
WHERE duplicate.id IS NULL;
havvg
  • 699
  • 1
  • 5
  • 4