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 una respuesta

Tu dirección de correo electrónico no será publicada.

* Please enter the Biggest Number