How Can I Delete Duplicate Rows in MySQL Without Receiving an Error?

Deleting Duplicate Rows in MySQL: A Unique Table Trick

Dealing with duplicate records in a database can be a common pain point. In MySQL, deleting these duplications can be achieved through a series of steps. Let's walk through a specific scenario and tackle the potential pitfalls along the way.

Suppose we have a table named "employee" with fields 'empid', 'empname', and 'empssn'. To identify duplicate records, we execute a query to count the occurrences of each 'empssn':

<code class="sql">SELECT COUNT(empssn), empssn FROM employee GROUP BY empssn HAVING COUNT(empssn) > 1</code>

This query will return rows indicating which 'empssn's appear more than once.

Next, we attempt to delete the duplicate records using the following query:

<code class="sql">DELETE FROM employee WHERE (empid, empssn) NOT IN (SELECT MIN(empid), empssn FROM employee GROUP BY empssn);</code>

Unfortunately, this query may encounter the error: "You can't specify target table 'employee' for update in FROM clause."

To circumvent this error, we can employ a technique that involves wrapping the inner query within a derived table:

<code class="sql">DELETE FROM employee WHERE (empid, empssn) NOT IN (SELECT empid, empssn FROM (SELECT MIN(empid) AS empid, empssn FROM employee GROUP BY empssn) X);</code>

This derived table effectively acts as a temporary table, allowing us to reference the target table 'employee' in both the DELETE statement and the FROM clause.

By implementing this trick, we can successfully delete duplicate records from the 'employee' table. This solution not only solves the technical hurdle but also provides a deeper understanding of database manipulation in MySQL.

The above is the detailed content of How Can I Delete Duplicate Rows in MySQL Without Receiving an Error?. For more information, please follow other related articles on the PHP Chinese website!