How to Delete Duplicate Rows from a MySQL Table (Versions 5.0 and Above)?

Deleting Duplicates from a MySQL Table

In MySQL, you may encounter a scenario where you need to remove duplicate rows from a table while ensuring unique identification through an ID column. While the provided query is valid for MySQL 5.7 and above, it doesn't work in earlier versions like 5.0.

To achieve the desired behavior in MySQL 5.0, you can rewrite the query using the IN operator rather than = for comparing IDs with the subquery result set. Additionally, you cannot directly modify a table from a subquery within the same query.

To resolve this limitation, you can either execute separate SELECT and DELETE queries or use nested subqueries to alias the result of the inner subquery. Here's an example of a nested subquery approach:

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

Alternatively, as suggested by Mchl, you can employ joins to delete duplicate rows by joining the table with itself based on the ID column and using the HAVING clause to filter for duplicate IDs.

The above is the detailed content of How to Delete Duplicate Rows from a MySQL Table (Versions 5.0 and Above)?. For more information, please follow other related articles on the PHP Chinese website!