⌘+k ctrl+k
1.4 (stable)
Search Shortcut cmd + k | ctrl + k
MERGE INTO Statement

The MERGE INTO statement is an alternative to INSERT INTO ... ON CONFLICT that doesn't need a primary key since it allows for a custom match condition. This is a very useful alternative for upserting use cases (INSERT + UPDATE) when the destination table does not have a primary key constraint.

Examples

First, let's create a simple table.

CREATE TABLE people (id INTEGER, name VARCHAR, salary FLOAT);
INSERT INTO people VALUES (1, 'John', 92_000.0), (2, 'Anna', 100_000.0);

The simplest upsert would be updating or inserting a whole row.

MERGE INTO people
    USING (
        SELECT
            unnest([3, 1]) AS id,
            unnest(['Sarah', 'John']) AS name,
            unnest([95_000.0, 105_000.0]) AS salary
    ) AS upserts
    ON (upserts.id = people.id)
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED THEN INSERT;

FROM people
ORDER BY id;
id name salary
1 John 105000.0
2 Anna 100000.0
3 Sarah 95000.0

In the previous example we are updating the whole row if id matches. However, it is also a common pattern to receive a change set with some keys and the changed value. This is a good use for SET.

MERGE INTO people
    USING (
        SELECT
            1 AS id, 
            98_000.0 AS salary
    ) AS salary_updates
    ON (salary_updates.id = people.id)
    WHEN MATCHED THEN UPDATE SET salary = salary_updates.salary;

FROM people
ORDER BY id;
id name salary
1 John 98000.0
2 Anna 100000.0
3 Sarah 95000.0

Another common pattern is to receive a delete set of rows, which may only contain ids of rows to be deleted.

MERGE INTO people
    USING (
        SELECT
            1 AS id, 
    ) AS deletes
    ON (deletes.id = people.id)
    WHEN MATCHED THEN DELETE;

FROM people
ORDER BY id;
id name salary
2 Anna 100000.0
3 Sarah 95000.0

MERGE INTO also supports more complex conditions, for example, for a given delete set we can decide to only remove rows that contain a salary bigger or equal than a certain amount.

MERGE INTO people
    USING (
        SELECT
            unnest([3, 2]) AS id, 
    ) AS deletes
    ON (deletes.id = people.id)
    WHEN MATCHED AND people.salary >= 100_000.0 THEN DELETE;

FROM people
ORDER BY id;
id name salary
3 Sarah 95000.0

If needed, DuckDB also supports multiple UPDATE and DELETE conditions.

-- Let's get John back in!
INSERT INTO people VALUES (1, 'John', 105_000.0);

MERGE INTO people
    USING (
        SELECT
            unnest([3, 1]) AS id,
            unnest([89_000.0, 70_000.0]) AS salary
    ) AS
    ON (upserts.id = people.id)
    WHEN MATCHED AND people.salary < 100_000.0 THEN UPDATE SET salary = upserts.salary
    -- Second update or delete condition
    WHEN MATCHED AND people.salary > 100_000.0 THEN DELETE
    WHEN NOT MATCHED THEN INSERT BY NAME;

FROM people
ORDER BY id;
id name salary
3 Sarah 89000.0

Syntax

About this page
© 2025 DuckDB Foundation, Amsterdam NL
Code of Conduct Trademark Use