How to Delete Duplicate Rows from an SQL Table without the \'You can\'t specify target table...\' Error?

Deleting Duplicate Rows from an SQL Table

In database management, it's often necessary to remove duplicate records from a table. MySQL provides a variety of ways to achieve this.

One common approach is to identify duplicate rows using a query like:

SELECT COUNT(empssn), empssn FROM employee GROUP BY empssn HAVING COUNT(empssn) > 1

This query identifies rows with duplicate values in the empssn column. To delete these duplicates, you can use the following query:

DELETE FROM employee WHERE (empid, empssn) NOT IN (SELECT MIN(empid), empssn FROM employee GROUP BY empssn);

However, this approach can result in the error "You can't specify target table 'employee' for update in FROM clause." To address this, you can wrap the subquery in a derived table:

DELETE FROM employee WHERE (empid, empssn) NOT IN (SELECT empid, empssn FROM (SELECT MIN(empid) AS empid, empssn FROM employee GROUP BY empssn) X);

Using a derived table allows you to reference the original table in the subquery without the error. This approach successfully deletes duplicate rows in the employee table while preserving the original data structure.

The above is the detailed content of How to Delete Duplicate Rows from an SQL Table without the \'You can\'t specify target table...\' Error?. For more information, please follow other related articles on the PHP Chinese website!