Generador de upsert

Upsert en PlanetScale: INSERT … ON DUPLICATE KEY UPDATE

Nombra tu tabla, clave y columnas y obtén la sentencia que PlanetScale 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 PlanetScale 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 PlanetScale

PlanetScale speaks MySQL over Vitess, so INSERT … ON DUPLICATE KEY UPDATE runs as written, including the IF() watermark guard. On a sharded keyspace the statement must be routable: the unique key you upsert on should be, or include, the sharding key, otherwise Vitess has to scatter the write across shards and may reject it.

Foreign key constraints are disabled by default, so relationships between synced entities are modelled with stable id columns rather than enforced FKs; the upsert does not need them. Schema changes go through deploy requests, which means the staging table should be created once and reused, not created and dropped per batch.

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

Does ON DUPLICATE KEY UPDATE work on sharded tables?

Yes, when the key includes the sharding column so the row maps to one shard. Cross-shard upserts on a secondary unique index are the case to avoid.

Can I use foreign keys to keep deals and contacts consistent?

Not by default. Land both entities with stable ids and join on them; referential integrity is checked at read time or in dbt tests, not by the database.

Why keep a permanent staging table?

Because DDL is a deploy request on PlanetScale. TRUNCATE the staging table between batches instead of recreating it.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en PlanetScale 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