Cómo utilizar subconsultas en las sentencias INSERT, UPDATE y DELETE

¿Sabías que las subconsultas también se pueden utilizar en las sentencias UPDATE, INSERT y DELETE? Las subconsultas en SQL son como bloques de construcción que pueden ser usados en una variedad de sentencias, no sólo SELECT. Si encuentras que las subconsultas son útiles en SELECT, lee este artículo para descubrir lo útiles que pueden ser en otras instrucciones. ¡Podrás crear consultas SQL mucho más complejas y potentes en poco tiempo!

Si quieres refrescar tus conocimientos sobre subconsultas, te recomiendo el curso interactivo Ejercicios prácticos de SQL curso interactivo. Contiene más de 80 ejercicios sobre subconsultas y otras construcciones desafiantes de SELECT.

Punto de Partida: Un Rápido Repaso a las Subconsultas

Comencemos con un rápido recordatorio de lo que es una subconsulta. Dado que las subconsultas se utilizan con mayor frecuencia en las sentencias SELECT, revisaremos un ejemplo de una subconsulta simple en una sentencia SELECT.

Podemos definir una subconsulta como una consulta dentro de otra consulta. Aunque las subconsultas se utilizan con mayor frecuencia en la cláusula WHERE de las sentencias SELECT, pueden utilizarse en otras cláusulas como WHERE, FROM y HAVING, entre otras.

Veamos la base de datos que utilizaremos como ejemplo. Imagina que eres el propietario de una tienda de vinos, y que tienes una base de datos sencilla con 3 tablas para gestionar el funcionamiento de la tienda.

La primera tabla es wineque almacena los productos que vende, con el nombre, el precio, el número de botellas en stock, etc. de cada vino. La segunda es orderque almacena los pedidos que recibimos de nuestros clientes, incluyendo el nombre del vino solicitado y la cantidad pedida, entre otra información.

wine

order

Supongamos que queremos obtener una lista de vinos de los que nunca hemos recibido un pedido. La consulta tendrá el siguiente aspecto:

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

La subconsulta devuelve los nombres de todos los vinos de los que hemos recibido pedidos. A continuación, la consulta externa, mediante el operador NOT IN, obtiene los nombres de los vinos que no se han incluido en ningún pedido. SQL es tan sencillo como potente. Si quieres repasar algunos conceptos de subconsulta, te sugiero el curso SQL para principiantes donde puedes encontrar una sección completa sobre subconsultas.

Primera parada: Uso de subconsultas en sentencias INSERT

Usemos ahora una subconsulta en una sentencia INSERT. Esto es muy común; la idea es insertar el conjunto completo de resultados de una subconsulta o una sentencia SELECT en una tabla. Como ejemplo, imaginemos que queremos crear facturas para todos los pedidos de vino que hemos recibido durante el día. A continuación se muestra una vista parcial de nuestra invoice tabla:

Supongamos que la fecha de hoy es el 28 de junio de 2020, y queremos insertar los registros de las facturas asociadas a los pedidos de hoy. Podemos utilizar la siguiente SELECT para generar los datos de las facturas:

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

El resultado de esta consulta es el siguiente:

Esto es exactamente lo que queremos insertar en la tabla invoice.

Si simplemente añadimos una cláusula INSERT antes de la consulta, podemos insertar el resultado de la consulta en la tabla winecomo podemos ver en el siguiente ejemplo:

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Tenga en cuenta lo siguiente:

• Tenemos que nombrar explícitamente las columnas de la tabla invoice tabla en la que estamos insertando.
• Las columnas de la lista SELECT deben estar en el mismo orden que las columnas de la tabla.
• Omitimos la columna invoice_id para permitir que la base de datos elija el siguiente valor utilizando un generador de secuencias por defecto.

Después de ejecutar la cláusula INSERT, la tabla invoice tendrá los nuevos registros de facturas de los pedidos de hoy. Podemos verlo a continuación, con los nuevos registros en rojo:

Supongamos que en un día determinado, accidentalmente ejecutamos el INSERT dos veces, y nuestros clientes recibieron dos facturas por cada pedido como resultado. No queremos repetir el mismo error en el futuro. Para evitarlo, añadimos una subconsulta a la sentencia INSERT para ver si ya existe una factura con el mismo order_id. La siguiente es la nueva versión de la consulta INSERT. La subconsulta añadida al final identifica las facturas que ya existen, y la consulta externa las descarta utilizando el operador NOT IN.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

Si quieres mejorar tus habilidades en el uso de las sentencias INSERT, UPDATE, y DELETE, te sugiero el curso Cómo añadir, modificar y eliminar datos con SQL donde podrás ver varios ejemplos de estas sentencias.

Segunda parada: subconsultas en sentencias UPDATE

Al igual que SELECT, la sentencia UPDATE puede tener una subconsulta en varios lugares o cláusulas. En una sentencia UPDATE, las dos cláusulas en las que más se utilizan las subconsultas son SET y WHERE.

En la cláusula SET es donde definimos el nuevo valor de la columna que está siendo modificada por la sentencia UPDATE. Podemos utilizar una subconsulta para obtener este nuevo valor, que puede ser seleccionado de cualquier tabla o cualquier subconsulta válida, siempre y cuando devolvamos un solo registro con una sola columna por cada registro que se actualice. El tipo de datos de la columna devuelta por la subconsulta debe ser del mismo tipo que el de la columna que se está modificando.

Vamos a crear un UPDATE para mantener actualizado nuestro stock de botellas de vino. Ejecutaremos un UPDATE al final del día para reflejar lo que hemos vendido hoy. El código se verá así:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Si no utilizamos una cláusula WHERE en la UPDATE, acabaríamos modificando todos los registros de la tabla vino, incluidos los registros de los vinos que no hemos vendido hoy. La subconsulta devuelve un NULL para cualquier vino que no se haya vendido hoy, y nos equivocaríamos SET la columna stock a NULL, ya que el resultado de la expresión "stock - NULL" es NULL. Tenemos que arreglar esto.

Hay dos enfoques para el arreglo. El primero es modificar la expresión SUM(quantity) para que devuelva un cero en lugar de NULL. Para ello, simplemente tenemos que utilizar la función COALESCE, así:

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

El segundo enfoque consiste en añadir una subconsulta en la cláusula WHERE para modificar sólo los vinos que se han pedido hoy y mantener la expresión SUM(quantity) tal como está. La siguiente consulta muestra este enfoque:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

Esta UPDATE no es ideal: esta cláusula SET utiliza una subconsulta correlacionada. Una subconsulta correlacionada es aquella que la base de datos debe ejecutar varias veces, una por cada fila que se modifica en la tabla. En nuestro ejemplo, la consulta obtiene el SUM(quantity) para cada vino vendido hoy. Aunque las subconsultas correlacionadas pueden ser potentes, es mejor evitarlas siempre que sea posible como cuestión de buenas prácticas. En este caso, podemos evitar la subconsulta correlacionada utilizando una cláusula FROM en el UPDATE, como podemos ver a continuación:

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

Como recurso complementario, sugiero el artículo Beginners Guide to the SQL Subquery, donde puedes aprender el ABC de las subconsultas a través de ejemplos de diversa complejidad.

Tercera Parada: Subconsultas en Sentencias DELETE

Con la sentencia DELETE, las subconsultas sólo se pueden utilizar dentro de una cláusula WHERE. Digamos que queremos eliminar los registros de los vinos para los que no hemos recibido ningún pedido en los últimos 6 meses. Podemos crear una subconsulta que devuelva los vinos vendidos en los últimos 6 meses, y luego identificar los registros que queremos eliminar en la tabla wine utilizando el operador NOT IN. Veamos cómo hace esto SQL:

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Ahora, supongamos que queremos eliminar los vinos cuyos pedidos totales en los últimos 6 meses fueron menores a 10 unidades. La sentencia DELETE tendrá el siguiente aspecto:

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

Aquí, la subconsulta devuelve la cantidad de botellas pedidas en los últimos 6 meses para cada vino. Comparando esta cantidad con 10, podemos determinar si un determinado vino debe ser eliminado.

Llegando al final del recorrido de las subconsultas

Las subconsultas son como bloques de construcción en SQL. Vimos como pueden ser usadas en una variedad de lugares, como en las sentencias SELECT o en cualquier sentencia de modificación como INSERT, UPDATE, y DELETE. Vimos como podemos usar una subconsulta en diferentes cláusulas y que hay diferentes maneras de usar subconsultas en las sentencias INSERT, UPDATE, y DELETE. Si quieres aprender más sobre subconsultas, te sugiero el artículo Subquery vs. CTE: A SQL Primer, en el que se explica un tipo de subconsulta llamada CTE con muchos ejemplos y detalles. Finalmente, hay dos cursos con mucha información útil sobre subconsultas con muchos ejemplos: SQL para principiantes donde se encuentra una sección completa sobre subconsultas, y Cómo añadir, modificar y eliminar datos con SQL que es un curso más avanzado. ¡Vamos a mejorar tus conocimientos de SQL!