Oracle Database / Intermediate: application SQL and evidence / Lesson 7

DML, MERGE and idempotent imports

Apply source changes deterministically while preserving business keys, freshness and retry safety.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Design a deterministic MERGE source and match key.
Separate state synchronization from additive event processing.

Distinguish replacement from accumulation

An import may describe the latest known shipment state or an event that adds one unit of work. Replacing a status with the same status can be naturally repeatable; adding a charge again is not. Define source identity, business key, source revision and replay behavior before writing DML. A file name alone is a poor identity when files can be renamed or partially delivered. Store a durable request or event key where duplicates matter. Reconciliation should compare accepted, rejected and pending source records, rather than declaring success solely because a statement returned without an error.

Match on a stable identity

MERGE chooses update or insert from its ON condition. The example treats tracking_code as a unique business key and source_version as an ordered revision for that key. A newer revision updates status; an equal or older revision leaves the existing row unchanged. This prevents an old file from overwriting newer state but assumes source versions are trustworthy and comparable. Enforce uniqueness in the target, and validate the source to one chosen row per key. MERGE is not an automatic deduplication engine and is not a substitute for deciding how concurrent writers coordinate.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

MERGE INTO shipment_state t
USING staged_state s ON (t.tracking_code = s.tracking_code)
WHEN MATCHED THEN UPDATE
 SET t.status = s.status, t.source_version = s.source_version
 WHERE s.source_version > t.source_version
WHEN NOT MATCHED THEN INSERT (tracking_code, status, source_version)
 VALUES (s.tracking_code, s.status, s.source_version);

Assumes staged_state is unique by tracking_code. Replaying the same revision makes no matched update; it does not validate additive event semantics.

Reject ambiguity before modifying the target

Two source rows with the same tracking code and different statuses require a policy, not arbitrary ordering. If revisions are unique, choose the greatest revision with a deterministic tie rule. If the same revision carries contradictory payloads, quarantine the conflict. A preliminary GROUP BY query reveals duplicate keys but does not freeze a source table against later changes; use a stable staged batch and documented transaction ownership. The duplicate inventory is diagnostic evidence rather than a repair command. Preserve enough source provenance to explain why a particular revision was accepted months later.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT tracking_code, COUNT(*) AS source_rows
FROM staged_state
GROUP BY tracking_code
HAVING COUNT(*) > 1
ORDER BY tracking_code;

Each returned key needs a documented resolution before the illustrative MERGE. A clean result is meaningful only for the same immutable batch.

Inspect affected rows and errors separately

A conditional UPDATE can express capacity reservation in one statement: reduce free_units only when enough remain. The caller checks whether exactly one row matched and interprets zero as a rejected transition, missing identifier or stale condition according to the API contract. A successful statement with zero affected rows is not an engine failure. Conversely, logging rejected rows during bulk loading is not permission to silently discard them. Define whether a batch is all-or-nothing or partially accepted, how rejected items are reported, and how a retry avoids repeating already committed effects.

A changed identity is a different operation

Oracle does not allow updating a column referenced by the MERGE ON condition in the matched update clause. More fundamentally, changing a tracking code may represent correction, aliasing or a new shipment; each has different audit consequences. Handle key corrections through an explicit reviewed workflow rather than bending the synchronization condition until it accepts them. Keep timestamps and revision numbers separate: wall clocks can move backward and different senders can disagree. A source-generated monotonic revision needs a defined scope, while a received_at timestamp records ingestion timing rather than business truth.

Common errors and fixes

ORA-30926

Why it happens: An unstable or ambiguous source can attempt incompatible matches for target rows.
How to fix it: Inspect duplicate source keys and define deterministic source selection.
Prevent it next time: Freeze each staged batch and validate key uniqueness before DML.

ORA-38104

Why it happens: The matched update attempts to change a column used in ON.
How to fix it: Use a separate reviewed key-correction operation.
Prevent it next time: Choose immutable match identities for routine synchronization.

Replay doubles a monetary adjustment

Why it happens: The source event was treated as an unkeyed additive update.
How to fix it: Record the event key and enforce one committed application per event.
Prevent it next time: Test duplicate delivery and uncertain commit outcomes explicitly.

Check your understanding

1. A version 8 status is stored and an import replays version 6. What should the example MERGE do?

Show worked solution

The key matches, but the matched WHERE predicate is false, so no update occurs. The existing version 8 remains. Record the stale source outcome if reconciliation requires it. This rule depends on comparable source revisions and does not establish that the payload itself is valid.

2. Two rows share key and version but disagree on status. Is choosing the last physical row defensible?