How to Delete Rows from a MySQL Table Based on a Select Statement's Results?

Deleting from a Select Statement in MySQL

Question:

How do I delete rows from a MySQL table based on the results of a select statement?

Code (MySQL 5.0):

DELETE FROM posts where>

Problem:

The above code does not work in MySQL 5.0, and you want to find a working solution. Your goal is to delete rows that do not have unique IDs.

Answer:

Using IN:

In MySQL, subqueries return result sets. To use the results of a subquery in a DELETE statement, you should use the IN operator instead of the equals sign (=) in your WHERE clause.

Modified Code:

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

Additional Considerations:

• You cannot modify the same table from a subquery within the same query.
• You can execute separate SELECT and DELETE queries or use nested subqueries to achieve the desired result.
• An alternative approach involves using joins, as suggested by Mchl:

DELETE FROM posts p1
WHERE p1.id = (
    SELECT p2.id 
    FROM posts p2
    WHERE p2.id = p1.id
    GROUP BY p2.id
    HAVING COUNT(p2.id) > 1
)

The above is the detailed content of How to Delete Rows from a MySQL Table Based on a Select Statement's Results?. For more information, please follow other related articles on the PHP Chinese website!