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?
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