Generador de upsert

Upsert en MySQL: INSERT … ON DUPLICATE KEY UPDATE

Nombra tu tabla, clave y columnas y obtén la sentencia que MySQL acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga MySQL de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · INSERT … ON DUPLICATE KEY UPDATE

INSERT INTO `deals` (`id`, `name`, `stage`, `amount`, `owner_id`, `updated_at`)
SELECT s.`id`, s.`name`, s.`stage`, s.`amount`, s.`owner_id`, s.`updated_at`
FROM `deals_staging` AS s
ON DUPLICATE KEY UPDATE
  `name` = IF(`deals`.`updated_at` < s.`updated_at`, s.`name`, `deals`.`name`),
  `stage` = IF(`deals`.`updated_at` < s.`updated_at`, s.`stage`, `deals`.`stage`),
  `amount` = IF(`deals`.`updated_at` < s.`updated_at`, s.`amount`, `deals`.`amount`),
  `owner_id` = IF(`deals`.`updated_at` < s.`updated_at`, s.`owner_id`, `deals`.`owner_id`),
  `updated_at` = IF(`deals`.`updated_at` < s.`updated_at`, s.`updated_at`, `deals`.`updated_at`);

Cómo funciona el upsert en MySQL

MySQL's upsert is INSERT … ON DUPLICATE KEY UPDATE. It triggers on any PRIMARY KEY or UNIQUE index violation, and the UPDATE branch can reference the row that failed to insert. The generated form selects from a staging table aliased as s and updates each column from s, wrapped in IF() so a row with an older updated-at leaves the stored values alone.

Two things trip people up. Affected-rows counts 1 for an insert, 2 for an update and 0 for a no-op, which matters when a driver uses it to report success. And InnoDB reserves an auto-increment value for every attempted insert, so a table upserted daily shows gaps in surrogate ids; that is expected, not a lost row. Use utf8mb4 on the target so CRM text with emoji survives the write.

Antes de ejecutarla

"Duplicate" means any PRIMARY KEY or UNIQUE index, not just the one you think of as the business key. A second unique index on email will silently merge rows you meant to keep apart.
The IF() guard keeps the existing row when the incoming updated-at is older. Without it the last write wins, whatever its timestamp.
Avoid REPLACE INTO for upserts: it deletes and re-inserts, which fires DELETE triggers, cascades foreign keys and burns auto-increment values.

Preguntas frecuentes

Why does ON DUPLICATE KEY UPDATE merge rows I did not expect?

It fires on every unique index, not only the primary key. A unique index on email will fold two contacts with the same address into one. Drop or relax secondary unique indexes on synced tables.

REPLACE INTO or ON DUPLICATE KEY UPDATE?

ON DUPLICATE KEY UPDATE. REPLACE deletes the old row and inserts a new one, which fires DELETE triggers, cascades foreign keys and changes the auto-increment id.

Is VALUES(col) still valid?

It works but is deprecated since MySQL 8.0.20. Referencing the staging alias (s.col) as generated, or the INSERT … AS new alias form, is the supported syntax.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en MySQL con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones