---
title: "UPSERT"
description: "UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause."
canonical: "https://motherduck.com/glossary/upsert/"
related:
  - title: "UPDATE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/update/"
  - title: "DuckDB, the great federator?"
    url: "https://motherduck.com/blog/duckdb-the-great-federator/"
  - title: "Glossary | MotherDuck Docs"
    url: "https://motherduck.com/docs/troubleshooting/glossary/"
---

# UPSERT

> UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause.

## Overview
"Upsert" (update + insert) solves the common problem of writing a batch of records where some rows are new and some already exist and need refreshing — without first checking which is which. Most modern SQL engines implement it via `INSERT ... ON CONFLICT`, based on a unique key or primary key:

```sql
INSERT INTO customers (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email;
```

`EXCLUDED` refers to the row that was proposed for insertion but conflicted, letting you reference its values inside the `DO UPDATE SET` clause. `ON CONFLICT (id) DO NOTHING` instead silently skips rows that already exist, without erroring or overwriting.

<glossary-callout guide="duckdb-cheatsheet-full" />

## DuckDB specifics
DuckDB implements the Postgres-style `INSERT INTO ... ON CONFLICT (columns) DO UPDATE SET ... / DO NOTHING` syntax, requiring a primary key or unique constraint on the conflict target column(s):

```sql
CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR, updated_at TIMESTAMP);

INSERT INTO customers VALUES (1, 'Ada Lovelace', now())
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at;
```

As of DuckDB 1.4, DuckDB also supports the SQL-standard `MERGE INTO` statement as a more flexible alternative for upserts — it can match on any custom condition rather than requiring a declared primary key, and can express insert, update, and delete actions in one statement:

```sql
MERGE INTO customers
USING staged_customers s ON customers.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.updated_at);
```