Manipular registros de tabla con referencia a sí misma

En ocasiones cuando queremos manipular – borrar o editar – los registros de una tabla no tenemos los identificadores – ids que generalmente son la llave primaria P.K. – para poder realizar la manipulación directamente.

Algo que podemos hacer es una subconsulta – consulta anidada – para que, con base en el criterio de la consulta, obtengamos los identificadores de los registros que queremos manipular.

El único detalle es que, cuando la tabla hace referencia a sí misma, probablemente se nos muestre el siguiente mensaje de error – sustituir ‘nombre_de_la_tabla’ con el nombre de la tabla que estás utilizando en tu consulta – :

You can't specify target table 'nombre_de_la_tabla' for update in FROM clause

En el siguiente ejemplo tenemos una tabla de categorías / subcategorías la cual utiliza una estructura de árbol para almacenar los datos.

Las categorías principales / base tienen el valor sin definir – NULL – en la columna parent_id.

Las subcategorías tienen el id de la categoría base en la columna parent_id, es decir, la categoría padre es un registro que se encuentra dentro de la misma tabla.

¿Qué tendríamos que hacer para actualizar la fecha de creación para las categorías relacionadas con xml?

-- Obtener el id de la categoría padre de XML
SELECT * FROM categorias WHERE nombre LIKE "%xml%";

-- Obtener los registros de la categoría y las subcategorías
-- En este caso el id de la categoría padre es 1
SELECT * FROM categorias WHERE id = 1 OR parent_id = 1;

-- Actualizar los registros 
UPDATE categorias SET creado = NOW() WHERE id = 1 OR parent_id = 1;

Esto puede funcionar bien, pero si queremos hacer funcionar las consultas anidadas para este propósito podríamos usar un alias de tabla para evitar el error.

-- Seleccionar el id de la(s) categoría(s) donde el valor que se encuentre en la columna parent_id o id 
-- sea el valor que esté en la columna parent_id de la categoría con el nombre que contenga el término xml  
SELECT id FROM categorias WHERE parent_id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%') OR id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%');

-- Si intentamos actualizar el valor de la tabla directamente nos marca un error 
UPDATE categorias SET actualizado = NOW() WHERE id IN (SELECT id FROM categorias WHERE parent_id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%') OR id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%')); 

-- Utilizando un alias de tabla 
-- La consulta funciona tanto para actualizar...
UPDATE categorias SET actualizado = NOW() WHERE id IN (
  SELECT id FROM (
    SELECT id FROM categorias WHERE parent_id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%') OR id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%')) 
  AS tabla_alias);

-- ...como también funciona para borrar:

DELETE FROM categorias WHERE id IN (
  SELECT id FROM (
    SELECT id FROM categorias WHERE parent_id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%') OR id IN (SELECT parent_id FROM categorias WHERE nombre LIKE '%xml%')) 
  AS tabla_alias) 

La manera en que funciona es la siguiente:

-- Realizamos la consulta interna para obtener el / los 
-- identificadores.

SELECT id FROM tabla WHERE columna LIKE "%término%"

-- El resultado de esa consulta es un subconjunto * de 
-- la tabla con la que estamos trabajando
-- * es la columna id, el número de renglones varía
-- dependiendo del término de búsqueda utilizado
--
-- Para que se pueda realizar la manipulación 
-- (actualización / borrado) en esa tabla tenemos
--  que convertir el resultado en otra tabla. 
--
-- La sintaxis básica de una consulta es: 
SELECT columna(s) FROM (tabla)

-- En este caso, la columna que queremos obtener es id
SELECT id FROM (tabla)

-- La tabla a la que hacemos referencia es 
-- a la tabla obtenida por el resultado de la consulta 
-- Es por eso que, en lugar del nombre de la tabla, 
-- vamos a envolver la consulta entre paréntesis

-- Después de la consulta le asignamos un nombre
-- como alias de la tabla. 
-- Podemos usar u omitir la palabra AS entre la consulta  
-- y el nombre del alias (es opcional).

SELECT id FROM (SELECT id FROM tabla
WHERE columna LIKE "%término%") alias_tabla

SELECT id FROM (SELECT id FROM tabla
WHERE columna LIKE "%término%") AS alias_tabla

-- Toda la consulta para actualizar
UPDATE tabla SET columna1 = valor1, columna2 = valor2 WHERE id IN (
SELECT id FROM (SELECT id FROM tabla
WHERE columna LIKE "%término%") AS alias_tabla);

-- Toda la consulta para borrar
DELETE FROM tabla WHERE id IN (
SELECT id FROM (SELECT id FROM tabla
WHERE columna LIKE "%término%") AS x);

De esa manera MySQL no considera que la tabla utilizada en la subconsulta es la misma tabla donde va a actualizar / borrar los registros y por eso lo puede hacer.

El archivo / script para crear la tabla está en https://gist.github.com/eamexicano/fbe63f74241004a98cae21cd00567ee6.