How to Delete Duplicate Rows in a MySQL Table When the Target Table is in the FROM Clause?

Deleting Duplicate Rows from a Table in MySQL

When dealing with a table containing duplicate records, it becomes necessary to remove them to maintain data integrity and efficiency. In MySQL, achieving this involves identifying and deleting duplicate rows, a process that can be accomplished through specific queries.

In your case, you've already determined duplicate records based on the field empssn using this query:

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

To delete the duplicate records, you attempted the following query:

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

However, you encountered an error because MySQL does not allow the target table to be specified in the FROM clause for an update query.

To resolve this issue, one approach is to 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
);

By using this modified query, you effectively create a temporary, derived table (X) containing the necessary data from the employee table. The main DELETE query can then reference this derived table to delete the duplicate rows based on the unique combination of empid and empssn. This approach should resolve the error you encountered and allow you to successfully delete the duplicate records.

The above is the detailed content of How to Delete Duplicate Rows in a MySQL Table When the Target Table is in the FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!