Merge Statement

MonetDB supports the MERGE statement feature of the SQL:2003 standard.
Merge statements allow one to conditionally insert, update and/or delete records in a target table using an input relation with a single statement.

The grammar is as follows:

merge_statement:
     [ WITH cte_list ]
     MERGE INTO qname [ [AS] target_alias ]
     USING input_rel [ [AS] source_alias ]
        ON search_condition
     merge_clause [ merge_clause ]

cte_list:
     ident [ column_list ] AS query_expression [ ',' cte_list ]

input_rel:
     joined_table_or_view
   | [LATERAL] table_producing_udf
   | [LATERAL] subquery

search_condition:
     search_condition OR and_exp
   | and_exp

merge_clause:
     WHEN MATCHED THEN UPDATE SET assignment_list
   | WHEN MATCHED THEN DELETE
   | WHEN NOT MATCHED THEN INSERT [ column_list ] [ { DEFAULT VALUES | VALUES row_values } ] }

In practice, an inner join is performed between the target table and the input relation. For each row in the input relation:

• if it matches a row in the target table, then that row gets either updated or deleted according to the actions specified in the merge_clause WHEN MATCHED;
• otherwise, it gets inserted according to the actions specified in the merge_clause WHEN NOT MATCHED.

If a row from the target table does not match any of the input relation, then nothing happens to that row. However, if multiple rows from the input relation match a single row in the target table, then an error is thrown, because if a row in the target table were modified multiple times within a single merge statement, the result would be ambiguous.

In a merge statement, the clause, WHEN MATCHED, will be applied for each pair of matching rows between the two relations, while the clause, WHEN NOT MATCHED, is applied for each row in the input relation that did not have a match in the target table.

The MERGE INTO command is used to make changes in one table (target) based on values matched from another (source). It can be used to combine insert and update or insert and delete operations into one command, instead of multiple INSERT and UPDATE/DELETE commands. You must specify a merge search condition which determines whether a record will be inserted or updated/deleted.

Example:

MERGE INTO ProductTarget T
     USING ProductSource S
        ON S.ProductID = T.ProductID
      WHEN MATCHED     THEN UPDATE SET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.Color
      WHEN NOT MATCHED THEN INSERT (ProductID, Name, ProductNumber, Color)
                            VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color);

Examples

Below, we show the main features of merge statements:

CREATE TABLE food_store (id INT, name TEXT, new_item BOOL);
INSERT INTO food_store VALUES (1, 'bread', false), (2, 'coffee', false), (3, 'tea', false), (4, 'butter', false), (5, 'chocolate', false);

CREATE TABLE releases (id INT, reason CLOB);
INSERT INTO releases VALUES (5, 'too warm to eat chocolate'), (6, 'end of chestnuts season');

CREATE TABLE incoming (id INT, name CLOB);
INSERT INTO incoming VALUES (6, 'ice cream');

MERGE INTO food_store AS to_update USING releases AS food_updates
   ON to_update.id = food_updates.id
   WHEN MATCHED THEN DELETE;
-- An ingredient got deleted, note that the second row of “releases” tables got no correspondence in “to_update”, so nothing happened for that row.

MERGE INTO food_store USING (SELECT id, name FROM incoming) AS new_food
  ON food_store.id = new_food.id
  WHEN NOT MATCHED THEN INSERT VALUES (new_food.id, new_food.name, true);
-- A new ingredient was added

-- Sanity check: the table food_store with the previous updates
    SELECT id, name, new_item FROM food_store;

TRUNCATE incoming;
INSERT INTO incoming VALUES (1, 'cookies'), (2, 'cake'), (7, 'peanuts');

MERGE INTO food_store USING (SELECT id, name FROM incoming) AS food_updates
  ON food_store.id = food_updates.id
  WHEN NOT MATCHED THEN INSERT VALUES (food_updates.id, food_updates.name, true)
  WHEN MATCHED THEN UPDATE SET name = food_updates.name, new_item = true;

-- Sanity check: the first two ingredients got renamed, plus another ingredient got added
    SELECT id, name, new_item FROM food_store;

TRUNCATE releases;
INSERT INTO releases VALUES (1, 'chocolate cookies'), (1, 'gluten cookies'); -- second row added by mistake!

MERGE INTO food_store USING (SELECT id, reason FROM releases) AS food_updates
  ON food_store.id = food_updates.id
  WHEN MATCHED THEN UPDATE SET new_item = true;
-- The following query will trigger an error:
-- MERGE UPDATE: Multiple rows in the input relation 'food_updates' match the same row in the target table 'sys.food_store'
-- Two rows from food_updates relation match a single row in food_store, which is ambiguous for the update

--- Sanity check: the table food_store is unchanged
SELECT id, name, new_item FROM food_store;

Implementation notes

The SQL standard allows optional search conditions to be added after the keyword MATCHED (of both WHEN MATCHED and WHEN NOT MATCHED), so that one can further separate actions that should be taken in this merge_clause. This feature is currently not supported.

Multiple WHEN MATCHED clauses or multiply WHEN NOT MATCHED clauses in a single merge_statement are not supported at the moment.

The INSERT statement part of a WHEN NOT MATCHED clause does not support subqueries.