borrar registros mysql a partir de una select en la misma tabla
Si intentamos borrar registros de una tabla a partir de una select
delete from customers where customers.customerNumber in
(
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
)
obtenemos el error:
[Err] 1093 – You can’t specify target table ‘customers’ for update in FROM clause
Existen dos maneras de borrar registros de una tabla a partir de una select de la misma tabla:
usando in
delete from customers where customers.customerNumber in
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t )
y con inner join
delete customers from customers inner join
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t ) tt on customers.customerNumber=tt.customerNumber
es importante decir que tabla borras, pues al hacer el join la sentencia no sabria cual borrar.