Upsert

An upsert writes a row if it does not exist and updates it if it does, in one statement. Databases expose it under different spellings — INSERT ... ON CONFLICT, MERGE, INSERT ... ON DUPLICATE KEY UPDATE — but the purpose is the same: make a write idempotent with respect to a key, so that repeating it produces the same end state rather than a duplicate row or an error. That property is why upserts are the backbone of data pipelines and integrations. A sync that re-reads a source, a webhook consumer that may receive the same event twice, a batch job that is retried after a partial failure — all of these are safe if the write is keyed and idempotent, and all of them produce duplicates if it is not. The same reasoning applies to a nightly load that overlaps the previous window on purpose, which is the standard way to tolerate late-arriving records. Two details decide whether an upsert behaves. It needs a real uniqueness constraint on the conflict key; without one the database has nothing to detect a conflict against, and application-level check-then-insert logic loses the race under concurrency. And the update branch has to say explicitly which columns it overwrites — blindly replacing every column will wipe fields the source does not know about, such as a locally-computed status or a value another system owns. High-volume upserts also cost more than plain inserts because of the index lookups involved, so bulk loads sometimes stage into a temporary table and merge once instead.