0180 SQL Merge in MonetDB

Introduction

Merge is used when we want to use records from one table to decide what records to modify in another table. Let's assume that we want to keep two tables synchronized. In that case, every change on the first table should be reflected on the second table.

For full reflection, all of the updates, deletes or inserts that are done on the A table should also be done on the B table. For partial reflection, we can do just the part of the full reflection. For example, new rows from the A table will also be added to the B table. Records updated or deleted in the table A, will not be updated or deleted in the table B.

Merge statement can not do the full reflection, but it can do partial reflections. We will se such examples bellow.

Sample Tables

We will create two tables. We will try to propagate all the changes in the table A to the table B. For start, we will enter two rows in the table A.

CREATE TABLE A ( Letter CHAR, Number INTEGER ); INSERT INTO A ( Letter, Number ) VALUES ( 'A', 1 ), ( 'B', 2 ); CREATE TABLE B ( Letter CHAR, Number INTEGER );

Partial Synchronization

Initial Synchronization with Merge INSERT

All the rows from the table A that do not exist in table B will be inserted into table B.

MERGE INTO B      USING A         ON B.Letter = A.Letter       WHEN NOT MATCHED THEN INSERT ( Letter, Number ) VALUES ( A.Letter, A.Number );

MERGE INTO targetTable USING sourceTable ON matchingCondition WHEN NOT MATCHED THEN INSERT RECORDS

Now, table B, has the same rows as the table A.

Merge with UPDATE

First, we will update the table A. We will change A1 to A4. UPDATE A SET Number = 4 WHERE Letter = 'A';

Then we'll push that change to the table B. Notice that we don't use 'WHEN NOT MATCHED'. Now we use 'WHEN MATCHED'.

MERGE INTO B           USING A                   ON B.Letter = A.Letter and A.Letter = 'A'       WHEN MATCHED THEN UPDATE SET Number = A.Number;

Merge with DELETE

We will update one record in the table A. UPDATE A SET Number = 8 WHERE Letter = 'B';

Now, we will delete all the rows from the table B, that do exist in the table A. Row A4 exists in both tables, so that row will be deleted.
This is not the behavior that we would expect. We are deleting matching rows.

MERGE INTO B      USING A         ON B.Letter = A.Letter and B.Number = A.Number       WHEN MATCHED THEN DELETE;

Merge Matrix

When rows are matched, we can do UPDATE or DELETE. When rows are not matched, we can do INSERT. I would expect DELETE to work with "Not matched", but it is not. It works with "Matched".

Complex Merge

The great thing about MERGE statement is because we can do two things in one statement. We can only do ( INSERT and UPDATE ) or ( INSERT and DELETE ).

Merge with INSERT and UPDATE

This time we will do INSERT and UPDATE at the same time.

MERGE INTO B           USING A                 ON B.Letter = A.Letter             WHEN NOT MATCHED THEN INSERT ( Letter, Number )                                                          VALUES ( A.Letter, A.Number )             WHEN MATCHED THEN UPDATE SET Letter = A.Letter,                                    Number = A.Number;

Merge with INSERT and DELETE

Currently, tables A and B are equal. We will add a row to table A to show how to use merge with INSERT and DELETE.

INSERT INTO A ( Letter, Number ) VALUES ( 'E', 5 );

MERGE INTO B      USING A                 ON B.Letter = A.Letter       WHEN NOT MATCHED THEN INSERT ( Letter, Number )                             VALUES ( A.Letter, A.Number )       WHEN MATCHED THEN DELETE;

Things That Don't Work in Merge

INSERT with Subquery

Subqueries are not supported in INSERT clause, inside of Merge statement. MERGE INTO B           USING A                 ON B.Letter = A.Letter             WHEN NOT MATCHED THEN INSERT SELECT * FROM A;

Several MATCHED, or NOT MATCHED sentences

We can only use one MATCHED clause and/or one NOT MATCHED clause. MERGE INTO B      USING A         ON B.Letter = A.Letter       WHEN MATCHED THEN DELETE       WHEN MATCHED THEN UPDATE SET Letter = A.Letter,                                    Number = A.Number;

Multiple matches

I will add row "E6" in the table "A" with a statement "INSERT INTO A VALUES ( 'E', 6 );". Now this table has rows E5 and E6. If we now apply MERGE statement, both rows E5 and E6 will try to update row E5 in the table B. This is not allowed and will fail.

MERGE INTO B USING A ON B.Letter = A.Letter WHEN MATCHED THEN UPDATE SET Letter = A.Letter, Number = A.Number;