How to Efficiently Delete Rows with Non-Unique IDs in MySQL 5.0?

Fixing MySQL Query for Deleting Non-Unique IDs

The provided query is not functional in MySQL 5.0. This article addresses this issue and offers alternative solutions to delete rows with non-unique IDs in MySQL.

Original Problem:

DELETE FROM posts WHERE>

Explanation:

This query attempts to delete rows from the "posts" table where the "id" matches the "id" of rows that appear more than once in the table. However, in MySQL 5.0, it is not possible to use a subquery as the condition in a DELETE statement.

Solutions:

1. Using IN Clause:

Instead of using "=", change the condition to "IN" to compare the "id" with the result set of the subquery.

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

2. Separate SELECT and DELETE Queries:

Separate the subquery (SELECT statement) from the DELETE statement and execute them sequentially.

SELECT id FROM posts GROUP BY id HAVING (COUNT(id) > 1) INTO @duplicate_ids;
DELETE FROM posts WHERE id IN (@duplicate_ids);

3. Nested Subquery with Aliasing:

Nest the subquery and alias the result to be used in the DELETE statement.

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

4. Using JOINs:

Use JOINs to compare the "id" of the table with the result set of the subquery.

DELETE a.id
FROM posts AS a
JOIN (
  SELECT id, COUNT(*) AS num
  FROM posts
  GROUP BY id
  HAVING num > 1
) AS b
ON a.id = b.id

The above is the detailed content of How to Efficiently Delete Rows with Non-Unique IDs in MySQL 5.0?. For more information, please follow other related articles on the PHP Chinese website!