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 ); |
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 | 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 |
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 ON B.Letter = A.Letter and A.Letter = 'A' |
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 |
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 VALUES ( A.Letter, 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 |
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 |
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 |