Why Does \'DELETE FROM employee WHERE (empid, empssn) NOT IN (...)\' Error When Removing Duplicates in MySQL?

Removing Duplicate Rows in MySQL Tables: Tackling the Error

To eliminate duplicate records from a table named "employee" that contains fields 'empid', 'empname', and 'empssn', first identify them using the query:

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

However, the subsequent deletion query:

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

encounters an error about specifying the target table 'employee' in the FROM clause.

Solution:

To address this error, wrap the subquery in 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 modification allows you to reference the original table within the subquery and successfully delete the duplicate rows.

The above is the detailed content of Why Does \'DELETE FROM employee WHERE (empid, empssn) NOT IN (...)\' Error When Removing Duplicates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!