How do I delete a row from a MySQL select?

Problem

This code does not work with MySQL 5.0; how can it be rewritten to work?

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

I’d like to get rid of any columns that don’t have a unique identifier. I should point you that most of the time there is only one id (I tried the in syntax and it doesnt work as well).

Asked by IAdapter

Solution #1

SELECT (sub)queries return results in the form of result sets. In your WHERE clause, you must use IN rather than =.

Furthermore, as this answer demonstrates, you cannot change the same table from a subquery within the same query. You can, however, either SELECT then DELETE in separate queries, or nest another subquery and alias the result of the inner subquery (which looks a little hacky):

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

Or, as Mchl suggests, use joins.

Answered by BoltClock

Solution #2

DELETE 
  p1
  FROM posts AS p1 
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)

Answered by Mchl

Solution #3

Inner join is a technique that can be used:

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  

Answered by Charif DZ

Solution #4

This is one solution for deleting all duplicates except one from each batch of duplicates:

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;

Answered by havvg

Post is based on https://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql