Mysql error:1093 - Can't specify target table for update in FROM clause

Question

I have a table employees in my database with corrupt entries.

I tried to delete them excuting:

delete from employees 
where id_boss= (
select id_worker from employees e
where surname= 'XXX')
AND basic_wage>1500

but I get the next error:

#1093 - You can't specify target table 'pracownicy' for update in FROM clause

How can I overcome this?

Answer 1

In MySQL you can't delete from a row where you are selecting from at the same time. To overcome this you can either use another subquery to hide this fact or you can turn this into a join like this

delete e_emp
from employees e_emp
join employees e_boss on e_boss.id_worker = e_emp.id_boss 
where e_boss.surname = 'XXX'
AND e_emp.basic_wage > 1500

Answer 2

It also correctly? because it works

DELETE e_emp FROM  employees e_emp, 
(SELECT id_boss FROM employees  WHERE surname='XXX') AS e_boss
WHERE e_emp.id_boss=e_boss.id_worker
AND e_emp.basic_wage>1500