SQL:2003 merge statements now supported in MonetDB

As of the Apr2019 release of MonetDB, we have implemented 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 with_list ] MERGE INTO table_name [ [AS] ident ]
      USING input_rel [ [AS] ident ]
      ON search_condition merge_clause [ merge_clause ]

input_rel:
  joined_table_or_view | table_producing_udf | subquery

merge_clause:
   { WHEN MATCHED THEN { UPDATE SET assignment_list | 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.

Demonstration

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;

id	name	new_item
1	bread	false
2	coffee	false
3	tea	false
4	butter	false
6	ice cream	true

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;

id	name	new_item
1	cookies	true
2	cake	true
3	tea	false
4	butter	false
6	ice cream	true
7	peanuts	true

TRUNCATE releases;

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

-- The following query will trigger an error: "MERGE DELETE: Multiple rows in
--   the input relation match the same row in the target table 'sys.food_store'".
-- Because two rows from food_updates relation match a single row in
--   food_store, which is ambiguous for the update
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;

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

id	name	new_item
1	cookies	true
2	cake	true
3	tea	false
4	butter	false
6	ice cream	true
7	peanuts	true

Implementation notes

Some implementation notes are left here:

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.

Developer and support

The merge statements extension is developed and supported by Pedro Ferreira, a software developer at MonetDB Solutions. Feel free to sign up and send your questions to the MonetDB users-list.