Upsert
A single operation that updates a row if its key already exists and inserts a new row if it does not. The name joins update and insert.
An upsert is only as good as its key. If the key arrives in a slightly different form, such as with a trailing space or without its leading zeros, a duplicate is inserted where an update was intended.
Where it comes up
- Duplicate keys with conflicting valuesTwo rows share an identifier that should be unique, but disagree on other fields.
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
See also
- Primary keyA column, or set of columns, whose value is unique for every row and identifies it: a customer number, an order ID.
- MergeCombining two or more records judged to describe the same thing into a single record. The word is also used for joining two tables, and for merged cells in a spreadsheet; in data cleaning the first meaning is the usual one.
- DuplicateA record that describes the same real thing as another record in the same dataset. An exact duplicate matches in every field; a near duplicate differs in spelling, format, or detail.