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

Deleting Non-Unique Rows in MySQL Using a Select Statement

The provided query syntax

DELETE FROM posts where> attempts to delete rows that do not have unique IDs from the posts table. However, this syntax is incompatible with MySQL 5.0.</p>
<p><strong>Rewriting the Query</strong></p>
<p>To make the query compatible with MySQL 5.0, it should be rewritten as follows:</p>
<p>`<pre class="brush:php;toolbar:false">DELETE FROM posts WHERE id IN (</p>
<pre class="brush:php;toolbar:false">SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )

)
`

Understanding the Revised Query

The revised query uses the IN operator to compare the id column of the posts table with the result set of the subquery. The subquery retrieves the IDs of rows that have more than one occurrence in the posts table, indicating non-unique IDs. By using the IN operator, the query selects only the rows with non-unique IDs for deletion.

Additional Considerations

As mentioned in the answer reference, it is essential to note that MySQL does not allow modifying the same table from a subquery within the same query. To circumvent this restriction, the query can be split into two separate queries: a SELECT query to identify the rows to be deleted, followed by a DELETE query to execute the deletion. Alternatively, a nested subquery with an alias can be used to allow for the deletion within a single query.

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