Oracle Tips - MERGE

 

Scenario

One system needs data from a master table shared across the enterprise to function properly. If every transaction accesses the master table, the cost is high and it is not recommended by the enterprise guidance to use master data. As a result, a table inside this system is cloned from that master table to fulfill the system's daily operation. In the meanwhile, its data is also synchronized with the master table through a batch during the night window. When a record is updated in the master table, the updates should be reflected in the cloned table. When a record is newly added into the master table, that record should be inserted into the cloned one. 

MERGE clause can carry out these tasks avoiding to use several UPDATE, INSERT or DELETE statements. 

Syntax:

MERGE INTO <Target Table>

USING <Source Table>    -- It can be a view or sub-query

ON (<Condition>)

WHEN MATCHED THEN

    UPDATE SET <Column 1> = <Value 1> [, <Column 2> = <Value 2>, ...]

    [WHERE Condition]

    [DELETE WHERE Condition]

WHEN NOT MATCHED THEN

    INSERT [(<Column 1> [, <Column 2> , ...])] VALUES (<Value 1> [, <Value 2>, ...])

    [WHERE Condition] 

;

 

Example

In this example, the data in master table POS_ALLPROD is merged into the cloned table POS_ALLPROD_CLONED. Their structures are illustrated as below.

Data that resides in the two tables is listed in the below diagram.

We execute a MERGE script stated as below. As we can see, the existent records in POS_ALLPROD_CLONED were updated accordingly. The new records in POS_ALLPROD were inserted into the clone table as well. 

MERGE INTO POS_ALLPROD_CLONED C

USING POS_ALLPROD M

ON (C.CUSTOMER_ID = M.CUSTOMER_ID)

WHEN MATCHED THEN

    UPDATE SET C.PRODUCT = M.PRODUCT, 

               C.POSITION = M.POSITION,

               C.REGION = M.REGION,

               C.VALUEDATE = M.VALUEDATE

WHEN NOT MATCHED THEN

    INSERT (C.CUSTOMER_ID, C.PRODUCT, C.POSITION, C.REGION, C.VALUEDATE) 

    VALUES (M.CUSTOMER_ID, M.PRODUCT, M.POSITION, M.REGION, M.VALUEDATE)

;

 

Next, we add some conditions to UPDATE clause and INSERT clause. Let's see what we'll get.

MERGE INTO POS_ALLPROD_CLONED C
USING POS_ALLPROD M
ON (C.CUSTOMER_ID = M.CUSTOMER_ID)
WHEN MATCHED THEN
    UPDATE SET C.PRODUCT = M.PRODUCT, 
               C.POSITION = M.POSITION,
               C.REGION = M.REGION,
               C.VALUEDATE = M.VALUEDATE
    WHERE C.PRODUCT = 'STOCK'
WHEN NOT MATCHED THEN
    INSERT (C.CUSTOMER_ID, C.PRODUCT, C.POSITION, C.REGION, C.VALUEDATE) 
    VALUES (M.CUSTOMER_ID, M.PRODUCT, M.POSITION, M.REGION, M.VALUEDATE)
    WHERE M.PRODUCT <> 'OPTION'
;

The STOCK record in the clone table was updated, on the contrary, the FUND record was not. All new records in POS_ALLPROD except OPTION ones were added into POS_ALLPROD_CLONED.

By adding DELETE WHERE clause under [WHEN MATCHED THEN] block, you can delete records in the clone table that meet the specified conditions. This is not demonstrated in this post.

The data is populated from one table to another table through MERGE, providing one way for data propagation when the operation system is integrated with the master system at database layer.