Upsert and Merge

An upsert inserts a new keyed row or updates the row already associated with that key. SQL MERGE is a related conditional matching statement that can express different actions, depending on the engine. They are not interchangeable syntax, and a join called merge in a DataFrame is a different operation.

A merge needs an unambiguous action for each target key; multiple source matches may fail or behave differently across engines and clauses. When every change carries the complete replacement state, select the latest source version per key before merging. Partial changes need a different preparation step. Starting from {status:open, amount:100}, applying {status:paid} and then {amount:80} should produce {status:paid, amount:80}. Keeping only the second patch leaves the status open. Accumulate patches in source order or reconstruct a complete after-image, distinguishing an omitted field from an explicit NULL. Increments and commands likewise cannot be reduced to the latest message unless their contract explicitly makes earlier messages redundant.

Batch preparation and destination version checks solve different problems. A deterministic keyed merge can still overwrite newer state when an older batch is replayed. For a complete order state, version 12 should replace version 11, while a delayed version 10 should not. A strict version guard ignores equal versions, so equal version 12 with a different amount needs an explicit conflict rule. Validate unique target keys, unambiguous staged keys and non-missing versions. Text timestamps are safe to compare only under a consistent, sortable representation and source protocol.

SQLite uses INSERT ... ON CONFLICT DO UPDATE for upsert. Its conflict target depends on a uniqueness constraint. A current-row upsert neither preserves every old version nor removes a key deleted at the source. History and ordered delete or tombstone handling need their own design. Also verify concurrency and retry behavior in the actual database.

Further reading

• SQLite UPSERT