ERROR 1093 (HY000): You can't specify target table 'employee_salary' for update in FROM clause

Question

Delete from employee_salary
where emp_id in(
          select emp_id from
          employee_salary 
          group by emp_id,project,salary 
          having count(*)>1
);
ERROR 1093 (HY000): You can't specify target table 'employee_salary' for update in FROM clause

How to solve this problem and why this query is nor running?

score 1 · Answer 1 · answered Jan 03 '18 at 05:43

One option here is to wrap the subquery in another subquery:

DELETE e.*
FROM employee_salary e
WHERE e.emp_id IN (SELECT emp_id FROM (SELECT emp_id
                                       FROM employee_salary
                                       GROUP BY emp_id, project, salary 
                                       HAVING COUNT(*) > 1) x);

The trick here is that the subquery on x will force MySQL to create a temporary table which is independent of the deletion happening on employee_salary.

score 1 · Accepted Answer · answered Jan 03 '18 at 05:46

Try this:

Delete from employee_salary
where emp_id in(
         select * from (
          select emp_id from
          employee_salary 
          group by emp_id,project,salary 
          having count(*)>1 ) 
        as alias
)

I am changing the name of the result set so it will work fine.

ERROR 1093 (HY000): You can't specify target table 'employee_salary' for update in FROM clause

2 Answers2