Merging Rows into a Table

Merging Rows into a Table

The MERGE statement is used to both update and insert rows in a table. The MERGE statement has a join specification that describes how to determine if an update or insert should be executed. Figure 6.4 shows the syntax of the MERGE statement.


The WHEN MATCHED predicate specifies how to update the existing rows. The WHEN NOT MATCHED predicate specifies how to create rows that do not exist.

In the following example, we have a new pricing sheet for products in category 33. This new pricing data has been loaded into the NEW_PRICES table. We need to update the PRODUCT_INFORMATION table with these new prices. The NEW_PRICES table contains updates to existing rows in the PRODUCT_INFORMATION table as well as new products. The new products need to be inserted and the existing products need to be updated.

   SELECT product_id,category_id,list_price,min_price
   FROM oe.product_information
   WHERE category_id=33;

   PRODUCT_ID CATEGORY_ID LIST_PRICE  MIN_PRICE
   ---------- ----------- ---------- ----------
         2986          33        125        111
         3163          33         35         29
         3165          33         40         34
         3167          33         55         47
         3216          33         30         26
         3220          33         45         36

   SELECT *
   FROM new_prices;

   PRODUCT_ID LIST_PRICE  MIN_PRICE
   ---------- ---------- ----------
         2986        135        121
         3163         40         32
         3164         40         35
         3165         40         37
         3166         50         45
         3167         55         50
         3216         30         26
         3220         45         36

We use the MERGE statement to perform an update/insert of the new pricing data into the PRODUCT_INFORMATION table, as follows:

   MERGE INTO oe.product_information pi
   USING (SELECT product_id, list_price, min_price
       FROM new_prices) NP
   ON (pi.product_id = np.product_id)
   WHEN MATCHED THEN UPDATE SET pi.list_price =np.list_price
                               ,pi.min_price = np.min_price
   WHEN NOT MATCHED THEN INSERT (pi.product_id,pi.category_id
                               ,pi.list_price,pi.min_price)
     VALUES (np.product_id, 33,np.list_price, np.min_price);

   PRODUCT_ID CATEGORY_ID LIST_PRICE  MIN_PRICE
   ---------- ----------- ---------- ----------
         2986          33        135        121 (updated)
         3163          33         40         32 (updated)
         3164          33         40         35 (inserted)
         3165          33         40         37 (updated)
         3166          33         50         45 (inserted)
         3167          33         55         50 (updated)
         3216          33         30         26 (updated)
         3220          33         45         36 (updated)