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 cannot synchronize updates, deletes and inserts at the same time. Merge can synchronize ( INSERT and UPDATE ), or ( INSERT and DELETE ). Image bellow give us an explanation for INSERT and UPDATE. MERGE will create left outer join between tables. It will match rows based on our condition. Merge will then add and update records in the table B, so that table B is the same as the table A.

For INSERT and DELETE, we do the similar thing. New rows will be added to the table B, but matched rows will be deleted from the table B.

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 only one row in the table A.

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

Initial Synchronization

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 one row, the same as table A.

Merge with INSERT

First, we will insert another row into the table A. INSERT INTO A ( Letter, Number ) VALUES ( 'B', 2 );

Then we will repeat the same MERGE statement as above. This new row will then appear in the 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 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.

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

Merge with INSERT and UPDATE

The real reason for existence of MERGE statement is because we can do two things in one statement. 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;