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 to use a whole row in the USING clause. This way, if there is a match, the row can be updated to the new row without further instuctions (WHEN MATCHED THEN UPDATE), and when there is no match, the row can be trivially inserted into the table (WHEN NOT MATCHED THEN INSERT).

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;

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. If the match condition uses a column that has the same name in the source and destination, the keyword USING can be used in the match condition.

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

FROM people
ORDER BY id;

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
    USING (id)
    WHEN MATCHED THEN DELETE;

FROM people
ORDER BY id;

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
    USING (id)
    WHEN MATCHED AND people.salary >= 100_000.0 THEN DELETE;

FROM people
ORDER BY id;

If needed, DuckDB also supports multiple UPDATE and DELETE conditions. The RETURNING clause can be used to indicate which rows were affected by the MERGE statement.

-- 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 upserts
    USING (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
    RETURNING merge_action, *;

In some cases, you may want to perform a different action specifically if the source doesn't meet a condition. For example, if we expect that data that is not present on the source shouldn't be present in the target:

CREATE TABLE target AS
    SELECT unnest([1,2]) AS id;

MERGE INTO target
    USING (SELECT 1 AS id) source
    USING (id)
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED BY SOURCE THEN DELETE
    RETURNING merge_action, *;

There is also the possibility of specifying WHEN NOT MATCHED BY TARGET. However, the behavior is, as you may expect, the same as WHEN NOT MATCHED since by default when specifying conditions, we look at the target.

Multiple Matching Source Rows

A MERGE INTO statement may not affect the same target row more than once. If a target row matches multiple source rows and a WHEN MATCHED clause applies (e.g., UPDATE or DELETE), the statement throws an error:

CREATE TABLE target (id INTEGER, v INTEGER);
INSERT INTO target VALUES (1, 0);
CREATE TABLE source (id INTEGER, v INTEGER);
INSERT INTO source VALUES (1, 10), (1, 20);

MERGE INTO target
    USING source
    ON target.id = source.id
    WHEN MATCHED THEN UPDATE SET v = source.v;

Invalid Input Error:
MERGE INTO command cannot affect the same target row more than once. A target row matched more than one source row; ensure the source rows are deduplicated or the ON condition is unique.

To avoid this, deduplicate the source rows or make the ON condition unique. Note that this differs from UPDATE ... FROM, which uses an arbitrary matching source row.

Syntax