Generador de upsert

Upsert en Oracle Database: MERGE INTO … USING

Nombra tu tabla, clave y columnas y obtén la sentencia que Oracle Database 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 Oracle Database de forma incremental.

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

Genera la sentencia

Sentencia · MERGE INTO … USING

MERGE INTO deals t
USING (SELECT id, name, stage, amount, owner_id, updated_at FROM deals_staging) s
  ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET
  t.name = s.name,
  t.stage = s.stage,
  t.amount = s.amount,
  t.owner_id = s.owner_id,
  t.updated_at = s.updated_at
  WHERE s.updated_at > t.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, name, stage, amount, owner_id, updated_at)
  VALUES (s.id, s.name, s.stage, s.amount, s.owner_id, s.updated_at);

Cómo funciona el upsert en Oracle Database

Oracle introduced MERGE in 9i and it remains the upsert. The source is any subquery, the ON clause is in parentheses, and each branch can carry its own WHERE: the generated statement puts the watermark test on the UPDATE branch so stale rows are ignored rather than written. Columns used in ON cannot appear in the UPDATE SET list.

For batch loads, direct-path insert (the APPEND hint on the INSERT branch) and parallel DML make MERGE scale with the table rather than with row count. Oracle treats an empty string as NULL, so a CRM field that was cleared and one that was never set look the same after the load; if that distinction matters, land a separate flag column.

Antes de ejecutarla

Columns referenced in the ON clause cannot be updated in the MATCHED branch; Oracle rejects it with ORA-38104.
ORA-30926 "unable to get a stable set of rows" means the USING source has more than one row per key: dedupe the staging select with ROW_NUMBER() first.
For large batches add the APPEND hint on the INSERT branch and enable parallel DML; the WHERE on the UPDATE branch keeps stale rows from overwriting newer ones.

Preguntas frecuentes

What causes ORA-30926 unable to get a stable set of rows?

The USING subquery returns more than one row for a key. Add ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) and keep rn = 1.

Can I update the key column?

No. ORA-38104 rejects updating any column referenced in the ON clause. Keys are stable by design; if a source renumbers ids, that is a delete plus insert.

Where does the DELETE go?

In the MATCHED branch: WHEN MATCHED THEN UPDATE SET … DELETE WHERE s.is_deleted = 1 removes rows the source marked deleted, in the same statement.

El mismo generador para otros destinos

Sáltate escribir el merge

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