MERGE INTO-Anweisung

Die MERGE INTO-Anweisung ist eine Alternative zu INSERT INTO ... ON CONFLICT, die keinen Primärschlüssel benötigt, weil sie eine eigene Match-Bedingung erlaubt. Das ist eine sehr nützliche Alternative für Upsert-Fälle (INSERT + UPDATE), wenn die Zieltabelle keinen Primärschlüssel-Constraint hat.

Beispiele

Zuerst legen wir eine einfache Tabelle an.

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

Der einfachste Upsert verwendet eine ganze Zeile in der USING-Klausel. Bei einem Treffer kann die Zeile ohne weitere Anweisungen auf die neue Zeile aktualisiert werden (WHEN MATCHED THEN UPDATE), und wenn es keinen Treffer gibt, kann die Zeile einfach in die Tabelle eingefügt werden (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;

id	name	salary
1	John	105000.0
2	Anna	100000.0
3	Sarah	95000.0

Im vorherigen Beispiel aktualisieren wir bei Übereinstimmung von id die ganze Zeile. Es ist jedoch auch ein häufiges Muster, ein Change Set mit einigen Schlüsseln und dem geänderten Wert zu erhalten. Dafür eignet sich SET. Wenn die Match-Bedingung eine Spalte verwendet, die in Quelle und Ziel denselben Namen hat, kann das Schlüsselwort USING in der Match-Bedingung verwendet werden.

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;

id	name	salary
1	John	98000.0
2	Anna	100000.0
3	Sarah	95000.0

Ein weiteres häufiges Muster ist ein Delete Set von Zeilen, das nur IDs der zu löschenden Zeilen enthält.

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

FROM people
ORDER BY id;

id	name	salary
2	Anna	100000.0
3	Sarah	95000.0

MERGE INTO unterstützt auch komplexere Bedingungen, zum Beispiel können wir für ein gegebenes Delete Set entscheiden, nur Zeilen zu entfernen, deren salary größer oder gleich einem bestimmten Betrag ist.

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;

id	name	salary
3	Sarah	95000.0

Bei Bedarf unterstützt DuckDB auch mehrere UPDATE- und DELETE-Bedingungen. Die RETURNING-Klausel kann verwendet werden, um anzuzeigen, welche Zeilen von der MERGE-Anweisung betroffen waren.

-- 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, *;

merge_action	id	name	salary
UPDATE	3	Sarah	89000.0
DELETE	1	John	105000.0

In manchen Fällen möchten Sie eine andere Aktion ausführen, wenn die Quelle eine Bedingung nicht erfüllt. Wenn wir zum Beispiel erwarten, dass Daten, die in der Quelle nicht vorhanden sind, auch im Ziel nicht vorhanden sein sollten:

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, *;

merge_action	id
UPDATE	1
DELETE	2

Es besteht auch die Möglichkeit, WHEN NOT MATCHED BY TARGET anzugeben. Das Verhalten ist jedoch, wie zu erwarten, dasselbe wie WHEN NOT MATCHED, weil wir beim Angeben von Bedingungen standardmäßig auf das Ziel schauen.

Syntax