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.

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

* Please enter the Biggest Number