Error de mysql: #1093 – You can’t specify target table ‘TABLENAME’ for update in FROM clause

Ayer en el curro me encontré con este error de mysql:

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

Estaba trabajando con la base de datos del oscommerce, quería borrar las categorías que no tuvieran ni categorías ni productos en su interior.

La sql es algo asi:

Delete from categories where
             (categories_id not in (select distinct(categories_id) from products_to_categories)
             and (categories_id not in (select distinct(parent_id) from categories)

Se que seria mas efectivo con EXISTS pero ese no es el tema ahora.

Es una condición simple, la primera parte evalúa si tiene algún producto y la segunda si tiene algún hijo (mas bien si alguna la tiene como padre), si la conviertes en select aparecen todos los productos que no tienen ni categorias hijas ni productos en su interior.

Pero MySql devuelve el error #1093.

Y que significa ese error?

Ese error significa que, en MySql, no puedes borrar ni editar una tabla si en las subconsultas consultas esa tabla. En la última linea de la documentación de MySql sobre el update dice:

Currently, you cannot update a table and select from the same table in a subquery.

En mi consulta para consultar si tiene categorias hijas consulto la misma tabla que quiero borrar, y como se comprenderá, no quiero borrar sólo las categorías sin productos porque tengo categorías contenedor que solo tienen categorías hijas…

La solución más elegante que encontré fue crearme una tabla auxiliar:

CREATE TABLE categorias_a_borrar (
      id INT
 );

y rellenarla con los ids de las categorias que queria borrar:

Insert into categorias_a_borrar
      select categories_id from categories where
              (categories_id not in
                     (select distinct(categories_id) from products_to_categories)
                     and (categories_id not in (select distinct(parent_id) from categories)

y después borrar las categorías:

Delete from categories where
               categories_id in (select id from categories_a_borrar)

Espero que le ahorre algo de tiempo a alguien :D