UPSERT (ON CONFLICT) y aplicaciones de INSERT masivo

Los datos que vamos a usar — stock y stock_in

UPSERT (una mezcla de UPDATE e INSERT —

la operación "actualizar si existe, insertar si no" en una sola sentencia) se implementa mediante la sintaxis INSERT … ON CONFLICT(clave) DO UPDATE.

Si la fila choca con una clave primaria o una restricción UNIQUE, se ejecuta el update; si no, se inserta tal cual.

Antes de los ejercicios, comprueba las definiciones de columnas y los datos de ejemplo de las dos tablas — stock y stock_in.

① Ejecuta PRAGMA table_info(stock); para ver nombres de columnas, tipos y la clave primaria de stock (UPSERT asume que sku es la clave primaria).

② Previsualiza las dos tablas con SELECT * FROM stock ORDER BY sku; y SELECT * FROM stock_in;.

ON CONFLICT DO UPDATE — actualizar si existe, insertar si no

Escribir INSERT INTO tabla(...) VALUES (...) ON CONFLICT(col_clave) DO UPDATE SET col = ... significa: si la fila que intentas insertar choca con la clave declarada en ON CONFLICT (la clave primaria o una columna UNIQUE), se ejecuta el update de DO UPDATE; si no hay colisión, la fila simplemente se inserta.

Dentro de DO UPDATE, un nombre de tabla especial excluded te permite referenciar "los valores de la fila que intentaste insertar".

Escribe qty = qty + excluded.qty y, en caso de colisión, el qty existente se incrementa con el qty que intentabas insertar.

Usa qty = excluded.qty para sobrescribir y qty = qty + excluded.qty para acumular, según lo que necesites.

-- Choca con sku existente → DO UPDATE suma al qty
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- Sku nuevo → sin colisión, simplemente se inserta
INSERT INTO stock(sku, name, qty, price)
VALUES ('A006', 'Marker', 3, 120)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

SELECT sku, name, qty FROM stock WHERE sku IN ('A001', 'A006');

Imagina el requisito: "en el procesado de entradas, suma al stock para los skus existentes y registra los nuevos para los skus desconocidos". (Ejecútalo correctamente y aparecerá la explicación.)

① Antes del UPSERT, ejecuta SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku; para confirmar que A001 existe y A006 no.

② Para el A001 existente, haz UPSERT con name Pen, qty 15, price 80. En caso de colisión, suma el valor insertado al qty existente (no sobrescribas).

③ Para el A006 desconocido, ejecuta el mismo tipo de UPSERT con name Stapler, qty 3, price 200 (sin colisión, así que se inserta).

④ Por último, vuelve a ejecutar el mismo SELECT y confirma que se sumó a A001 y se insertó A006.

DO NOTHING — no hacer nada en caso de colisión

Cuando no quieres ni actualizar ni insertar — es decir, "saltar en silencio en caso de colisión" — usa ON CONFLICT(clave) DO NOTHING.

Sin colisión la fila se inserta; en colisión la fila se ignora sin error.

En el ejemplo siguiente, intentar insertar el A002 existente con DO NOTHING choca y se ignora, mientras que el nuevo A007 se inserta.

-- Ignorar en caso de colisión (DO NOTHING)
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 conserva sus valores originales (qty 60 / price 250)
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

Imagina el requisito: "al cargar varias filas en el maestro de stock, no sobrescribas nunca los skus existentes y añade solo los desconocidos".

① Antes del UPSERT, ejecuta SELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku; para confirmar que A003 existe y A007 no.

② Intenta insertar el A003 existente con name Clip, qty 0, price 0, usando DO NOTHING para que se ignore en caso de colisión.

③ Añade el A007 desconocido con name Eraser, qty 50, price 90 usando el mismo UPSERT con DO NOTHING.

④ Por último, vuelve a ejecutar el mismo SELECT y confirma que A003 no cambia y que se añadió A007.

UPSERT multifila — combina INSERT masivo con ON CONFLICT

Cuando añades ON CONFLICT a un INSERT multifila — VALUES (...),(...),(...) separados por comas — cada fila, de forma independiente, "actualiza en colisión, inserta si no".

Puedes reflejar datos de entradas en una sola sentencia, reemplazando un bucle procedimental que ramifica entre UPDATE e INSERT por fila con un único SQL.

Incluso en un UPSERT multifila, excluded sigue significando "el valor que intentaste insertar para esta fila", así que escribir qty = qty + excluded.qty aplica "sumar para las filas existentes, insertar tal cual para las nuevas" fila a fila.

El ejercicio final de este artículo es un UPSERT masivo de las 3 filas de stock_in (A001 / A004 / A006).

-- UPSERT multifila: las filas existentes acumulan qty y refrescan price; las nuevas se insertan
INSERT INTO stock(sku, name, qty, price)
VALUES
  ('A002', 'Note', 10, 260),
  ('A005', 'Glue', 20, 190),
  ('A007', 'Ruler', 15, 90)
ON CONFLICT(sku) DO UPDATE
  SET qty = qty + excluded.qty,
      price = excluded.price;

SELECT sku, name, qty, price FROM stock ORDER BY sku;

Imagina el requisito: "refleja las 3 filas de la tabla de entradas stock_in en stock con una sola sentencia". Este es el ejercicio final del artículo.

① Antes del UPSERT, ejecuta SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku; para confirmar que A001 / A004 existen y A006 no.

② Contra stock, inserta 3 filas en un único INSERT — A001 (name Pen / qty 50 / price 80), A004 (name Tape / qty 100 / price 150), A006 (name Marker / qty 30 / price 120) — y escribe un UPSERT masivo con ON CONFLICT(sku) DO UPDATE que sume al qty en caso de colisión.

③ Por último, coloca SELECT sku, name, qty FROM stock ORDER BY sku; y confirma que se sumó a A001 / A004 y que A006 se añadió nuevo.