Home | Blog | What Is an Upsert? Meaning, Examples, and Use Cases 

What Is an Upsert? Meaning, Examples, and Use Cases 

OneTeg

A supplier sends its full catalog every Monday. In week one, 8,000 products load cleanly. In week two, the same file arrives with 40 price changes and 12 new items. If the sync simply adds every row, the catalog now holds over 16,000 products, and half of them are duplicates. An upsert prevents this. It is a single operation that updates a record if it already exists and inserts it if it is new. 

The word combines “update” and “insert.” A match key, such as a SKU, tells the system which records already exist. 

What Does Upsert Mean? 

For each incoming record, an upsert asks one question: is this already here? If yes, it updates the existing record with the new values. If no, it creates a new record. 

Here is how the Monday catalog file would be handled: 

Incoming record Already in the catalog? Upsert result 
SKU-1001 with a new price Yes Price updated 
SKU-2040 No New product created 
SKU-1002 with no changes Yes Record stays the same 

The outcome is predictable. Running the same upsert twice with the same data gives the same result as running it once. Engineers call this idempotent. For business teams, it means a retried job or a schedule that fires twice will not create duplicates. 

Upsert vs Insert vs Update 

All three operations write data. They behave differently when a record already exists. 

Operation If the record exists If the record is new Risk when a job runs again 
Insert Adds a second copy, or fails Creates the record Duplicates 
Update Changes the record Does nothing New records are missed 
Upsert Changes the record Creates the record None, when the key is unique 
  • Upsert vs insert. Use insert when you know every record is new, such as a first load into an empty system. Use upsert when the same record may arrive again. Most recurring syncs fall into this group. 
  • Upsert vs update. An update only works on records that already exist. New records are skipped. An upsert handles both cases in one step, so the workflow needs no separate check and no branch. 

The Match Key Decides Everything 

The match key is the field an upsert uses to compare records. Choosing it well matters more than anything else in the setup. 

A good match key has three qualities. It is unique, so two records never share the same value. It is always present, because a record with an empty key cannot be matched. And it is shared by both systems, in the same format. 

Common match keys include SKUs, product codes, email addresses, order numbers and external IDs copied over from another platform. 

The third quality causes the most trouble in content and product stacks. A PIM may treat the SKU as its main key. A DAM may use an asset ID and keep the SKU as optional metadata. If the SKU is missing or written differently, the upsert cannot find the right record. We covered this in How Integration Layers Improve AI Data Hygiene. 

Where Are Upserts Used? 

Upserts appear wherever data is synced on a schedule or after an event. 

  • Product data syncs. A PIM sends changed products every night. New SKUs are created in the commerce platform, and existing ones get the latest names, prices and attributes. 
  • Asset metadata updates. New tags, rights dates or approval status reach the DAM or CMS, matched on asset ID or SKU. 
  • Supplier and partner feeds. A full catalog file arrives every week. The upsert applies only the changes instead of doubling the table. 
  • Reference tables. Country codes, channel names and category mappings change slowly. An upsert keeps exactly one row per code. 
  • AI workflow memory. An automation records which items it has processed, so the next run continues where the last one stopped. Agentic Memory: Where AI Automations Keep State explains this pattern. 
  • Recovery after a failure. When a job stops halfway, it can run again with the same data and create no duplicates. 

Most databases and business apps support upserts in their own way. PostgreSQL uses an INSERT command with an ON CONFLICT clause, and Salesforce offers an upsert operation that matches on an External ID field. The idea is the same in each case. 

Common Upsert Mistakes 

Most upsert problems start with the data. 

  • A key that is not unique. If two rows share a SKU, the system cannot tell which one to update. Mark the key field as unique in the target. 
  • Empty keys. Records without a key should be rejected or sent for review, never inserted blindly. 
  • Different key formats. “SKU-01005” and “sku01005” do not match. Normalize keys before the upsert runs. 
  • Blank values overwriting good data. Send only the fields the source system owns, so empty fields do not wipe out values maintained elsewhere. 
  • Expecting deletes. A standard upsert creates and updates records. Removing records needs a separate step. 

How OneTeg Handles Upserts 

The OneTeg Data Store is built into the platform, so flows can keep tables in sync without a separate database. Upserts are central to how those tables stay current. 

First, you choose a match key. In the field designer, you mark a field such as SKU as a unique key. If you do not name a key, the table’s primary field is used. 

Next, a flow step uses the Upsert rows action of the Data Store connector. It writes a batch of up to 200 rows in one call, creating new rows and updating existing ones by key. The response reports how many rows were created and updated. The connector needs no connection setup. 

OneTeg checks the whole batch before writing anything. If a row has no value for the key field, the step is refused with a clear message, and nothing is written. Sending the same batch twice gives the same result, so reruns and duplicate triggers add no copies. 

CSV imports follow the same idea. When you reload a file, choose Update rows matching a key field instead of Append every row. A dry run checks a sample before anything changes. Files up to 2 GB are supported, and about 1 million rows load in roughly 13 seconds. 

A typical product sync flow has three steps: 

  • Read changed products in the PIM. 
  • Upsert them into a Data Store table keyed on SKU. 
  • Pass the current rows to the next step, such as a DAM metadata update or a report. 

      Without upsert, the same flow would need a lookup, a condition and separate insert and update steps for every record. The Data Store user manual covers each action. Batch Processing iPaaS for Scalable Workflows and the Product Data Synchronization use case show how these syncs run at scale. 

      Upsert FAQ 

      What is an upsert in simple terms? 

      An upsert writes a record once. If a record with the same key already exists, it is updated. If not, a new record is created. 

      What is the difference between upsert and insert? 

      An insert always creates a new record. An upsert checks the match key first and updates the existing record when there is one, so it avoids duplicates. 

      Does upsert delete records? 

      No. A standard upsert only creates and updates records. Records removed at the source need a separate delete step. 

      What happens if the match key is not unique? 

      The system cannot tell which record to update. Most databases and integration tools refuse the upsert until the key field is marked unique. 

      Is upsert the same as merge? 

      MERGE is the SQL statement some databases, such as SQL Server and Oracle, use for upserts. It can also delete rows under set conditions, which a basic upsert does not do. 

      Keep Synced Data Clean with Upserts 

      Upserts keep synced data clean. They hold one record per key, handle new and existing records in one step and make retries safe. Pick a unique, shared match key, normalize it before syncing and let the upsert do the rest. 

      Contact us to schedule a OneTeg demo and see how the OneTeg Data Store keeps your product, asset and reference data in sync. 

      Search

      Categories

      Social Links