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.

For order A, 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.

Reference: SQLite UPSERT.