Respuesta

SQL proporciona diferentes formas de actualizar o insertar datos en una tabla:

UPDATE — modifica los registros existentes que cumplen con la condición.
MERGE (o UPSERT) — combina la lógica de inserción y actualización en una sola instrucción: si se encuentra la fila, actualiza; si no, añade.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) o INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — inserta una nueva fila y, en caso de conflicto por clave, actualiza la existente.

Cuándo y qué utilizar:

Si se sabe con certeza que no hay registros — utiliza INSERT.
Para actualizar registros existentes — UPDATE con condiciones.
Si no se sabe si existe el registro, es más eficiente utilizar MERGE o UPSERT — esto ahorra complejidad y reduce la cantidad de consultas SQL.
Las operaciones UPSERT suelen ser más rápidas con grandes volúmenes de registros y minimizan las esperas.

Ejemplo (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Impacto en el rendimiento y la integridad:

Múltiples UPDATE/INSERT separados pueden llevar a bloqueos y conflictos.
MERGE/UPSERT son más eficaces en migraciones masivas y mantienen la atomicidad.

Pregunta trucada

¿Qué sucederá si se intenta realizar un UPSERT en un campo único, pero al mismo tiempo se inserta y actualiza la misma fila en la misma transacción?

Respuesta correcta: Puede ocurrir un error de bloqueo/conflicto, ya que la transacción intentará modificar la misma fila dos veces. La operación no se completará o finalizará con un error deadlock dependiendo del SGBD.

Ejemplo:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

Con un orden y nivel de aislamiento incorrectos, puede haber un conflicto de cambios.

Historia #1

En una lógica de migración de datos, se utilizó una combinación de SELECT y luego INSERT o UPDATE, lo que a veces provocaba que otra transacción estableciera los datos antes, y ocurría un error de "clave duplicada". Esto provocó frecuentes fallos en la exportación nocturna de datos, fue necesario reemplazar la lógica por UPSERT.

Historia #2

En un proyecto en MySQL se utilizó incorrectamente ON DUPLICATE KEY UPDATE, que no consideraba correctamente las claves únicas compuestas. Como resultado, parte de los datos no se actualizaba y aparecían duplicados. El problema solo se pudo identificar en el entorno de producción.

Historia #3

En un proyecto se utilizó UPDATE en lugar de UPSERT para la sincronización de datos del usuario. En caso de fallos de conexión, se perdía parte de los datos, ya que se omitía la actualización para nuevos usuarios. Después de un análisis, se implementó completamente MERGE/UPSERT.

Explique las diferencias entre las instrucciones UPDATE, MERGE e INSERT ... ON DUPLICATE KEY UPDATE en SQL. ¿En qué casos es preferible cada enfoque y cómo afectan estas herramientas al rendimiento y la integridad de los datos?

Respuesta

Pregunta trucada