UPDATE / DELETE con subconsultas y JOIN

Los datos que vamos a usar — stock y stock_in

Este artículo cubre un estilo de escritura más avanzado — actualizar y eliminar usando valores de otra tabla o un resultado agregado.

En concreto, tres patrones: actualizaciones cuyos valores se calculan con una subconsulta correlacionada (una subconsulta cuyo SELECT interno referencia columnas de la fila externa, por lo que se evalúa por cada fila), actualizaciones tipo join con UPDATE … FROM contra otra tabla, y sentencias DELETE con una subconsulta en la cláusula WHERE.

Antes de los ejercicios, échale un vistazo a las definiciones de columnas y a los datos de ejemplo de las dos tablas — stock y stock_in.

① Ejecuta PRAGMA table_info(stock); para comprobar nombres de columnas, tipos y la clave primaria de stock.

② Previsualiza ambas tablas con SELECT * FROM stock LIMIT 5; y SELECT * FROM stock_in;. Fíjate en que stock_in contiene un sku que no existe en el lado stock.

Usa una subconsulta correlacionada para actualizar con valores de otra tabla

Escribir UPDATE tabla SET col = (SELECT ... WHERE subconsulta.clave = tabla.clave) evalúa el SELECT interno una vez por fila objetivo y escribe el resultado en la columna.

Como el SELECT interno referencia una columna del objetivo externo del UPDATE (stock.sku), se llama subconsulta correlacionada.

La trampa es esta: para las filas de stock que no tienen fila coincidente en stock_in, el SELECT interno devuelve NULL.

-- Sin COALESCE: qty se sobrescribe con NULL para los skus sin coincidencia
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

Si ejecutas qty = qty + (SELECT ...) tal cual, las filas sin entrada de mercancía ven su qty sobrescrito con NULL.

Para evitarlo, o envuelves la subconsulta en COALESCE(subconsulta, 0) para convertir NULL en 0, o limitas el objetivo con WHERE EXISTS (...) para tocar solo las filas que realmente tienen una entrada.

-- Primero previsualiza los valores tras el update con SELECT (stock no se modifica)
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- Cuando lo veas bien, pásalo a UPDATE (COALESCE convierte NULL en 0)
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

Imagina el requisito: "refleja las cantidades de la tabla de entradas stock_in en el qty de las filas coincidentes de stock." (Ejecútalo correctamente y aparecerá la explicación.)

① Primero ejecuta SELECT sku, qty FROM stock ORDER BY sku; para ver el estado antes de cualquier actualización.

② Escribe la versión con COALESCE: una actualización con subconsulta correlacionada que suma stock_in.add_qty a stock.qty. Para los skus sin fila coincidente en stock_in, trata NULL como 0 para que su valor no cambie. Ejecuta otro SELECT después para confirmarlo.

③ Después escribe la versión con EXISTS: una actualización que usa WHERE EXISTS (...) para limitar el objetivo a "filas que tienen una entrada". Ejecuta SELECT otra vez y fíjate en que A001 / A004 reciben la misma entrada por segunda vez — se cuentan dos veces.

UPDATE … FROM: combina otra tabla con un join y actualiza

Escribir UPDATE tabla SET col = valor FROM otra_tabla WHERE condicion_join te permite combinar el objetivo del UPDATE con la tabla nombrada en FROM y actualizar de una sola vez, referenciando las columnas del lado del join directamente en SET.

-- Ejemplo de UPDATE … FROM: sube price en 10 solo para los artículos que tienen una entrada
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- Solo los skus que hicieron join (A001 / A004) ven subir su price
SELECT sku, price FROM stock ORDER BY sku;

Las filas de stock que no tienen coincidencia en el lado FROM (stock_in) fallan la condición de join en WHERE y quedan fuera del objetivo del update automáticamente.

A diferencia de la forma con subconsulta correlacionada, no necesitas protegerte de NULL por separado.

Imagina el requisito: "haz el mismo reflejo de stock del Ejercicio 1, pero escríbelo más compacto con UPDATE … FROM."

① Pon stock como objetivo del update y stock_in en el lado FROM, haz join por sku y escribe un UPDATE … FROM que sume add_qty a qty.

② Termina el script con SELECT sku, qty FROM stock ORDER BY sku; y confirma que solo los skus que hicieron join (A001 / A004) ven aumentado su stock.

DELETE con condición por subconsulta y comprobación del número de filas afectadas

Puedes poner una subconsulta en el WHERE de un DELETE, como DELETE FROM tabla WHERE col IN (SELECT ...) o WHERE EXISTS (SELECT ...).

Esto permite eliminaciones que no se pueden expresar con un valor fijo: "elimina solo las filas que existen / no existen en otra tabla" o "elimina filas cuyo resultado agregado cumple una condición".

La eliminación es destructiva e irreversible, así que la práctica habitual en producción es ejecutar primero el mismo WHERE dentro de un SELECT COUNT(*) para comprobar el número de filas afectadas, y luego reescribir a DELETE.

Después de ejecutar, verifica con SELECT COUNT(*) FROM tabla; para confirmar que el número de filas restantes coincide con lo que esperabas.

-- Primero comprueba qué se eliminaría: artículos por debajo del precio medio (stock no se modifica)
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- Cuando lo veas bien, pásalo a DELETE y comprueba el número restante
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

Imagina el requisito: "elimina de stock los candidatos a descatalogar — skus cuyo qty es 0 y no tienen entrada programada en stock_in." Como es el último ejercicio del artículo, harás una eliminación destructiva contra la tabla en vivo.

① Escribe un DELETE con condición por subconsulta que elimine de stock las filas en las que qty es 0 y sku no existe en stock_in.

② Tras el delete, ejecuta SELECT COUNT(*) AS remaining FROM stock; para confirmar cuántas filas quedan. También puedes usar SELECT * FROM stock ORDER BY sku; para verificar que A005 (Glue / qty 0 / sin entrada) ya no está.