Qué hacer cuando una modificación requiere consultar la tabla que se está modificando

En ocasiones, para modificar una tabla (con update, insert, delete), necesitamos consultar algún dato de la la propia tabla para usarlo en la instrucción de modificación. En MySQL esto no es posible, salvo que emplees este truco.

Supongamos que disponemos de esta tabla, Articulo, con datos de precio de ciertos artículos:

+----+--------+------+
| id | precio | id_p |
+----+--------+------+
|  1 |     10 |    1 |
|  2 |     25 |    2 |
|  3 |     50 |    2 |
+----+--------+------+

Queremos actualizar el precio del artículo 3, haciéndolo igual al precio más barato de entre todos los artículos de la tabla. Podríamos intentar ejecutar la siguiente instrucción, que obtiene el mínimo precio usando una subconsulta:

MAL

update Articulo
set precio = (select min(precio)
              from Articulo )
where id = 3;

Al ejecutarla, obtendremos un error como el siguiente:

ERROR 1093 (HY000): You can't specify target table 'Articulo' for update in FROM clause

El error es debido a que, en MySQL, no puede modificar una tabla y al mismo tiempo consultarla en una subconsulta. Este aspecto está documentado en: https://dev.mysql.com/doc/refman/8.0/en/update.html.

Una posible solución es sustituir la tabla en la subconsulta por una tabla derivada equivalente. Es decir, en el ejemplo anterior, podemos cambiar la tabla Articulo por su equivalente "select * from Articulo". Recuerda que se llama tabla derivada a las subconsultas que se colocan en el from, que actúan como tablas virtuales sobre las que realizar otras consultas. Para usarlas es necesario darles un alias. Así, la siguiente consulta sí es correcta:

BIEN

update Articulo
set precio = (select min(precio)
              from (select * from Articulo) A)
where id = 3;

Lo anterior se aplica a instrucciones update, delete e insert (salvo en su version insert - select). He aquí unos ejemplos:

MAL

delete 
from Articulo
where precio = 
      (select min(precio)
       from Articulo);

BIEN

delete 
from Articulo
where precio = 
      (select min(precio)
       from (select * from Articulo) A);

MAL

insert into Articulo 
values (4, 
        (select min(precio) 
         from Articulo), 
        2);

BIEN

insert into Articulo 
values (4, 
        (select min(precio) 
         from (select * 
               from Articulo) A), 
        2);

BIEN

insert into Articulo
select 4, min(precio), 2
from Articulo;