How to Use Merge Statement in SQL Query: Best Examples

The MERGE statement is a relatively new concept that was first introduced in Oracle 9i. MERGE inserts a row if it does not yet exist and updates specified columns based on given criteria if the target row has previously been inserted.

All major RDBMS databases supports Merge statement. Benefit of the MERGE is you can insert all INSERT, UPDATE and DELETE statements in single query.

1. Merge UPDATE and INSERT

MERGE INTO local_product lp 
    USING product p ON (lp.prod_id_n = p.prod_id_n ) 
    WHEN MATCHED THEN UPDATE 
    SET      lp.prod_price_n = p.prod_price_n, 
             lp.prod_status_s = p.prod_status_s, 
             lp.prod_pltwid_n = p.prod_pltwid_n, 
             lp.prod_pltlen_n = p.prod_pltlen_n, 
             lp.prod_shipweight_n = p.prod_shipweight_n 
    WHEN NOT MATCHED THEN INSERT 
    VALUES   (p.prod_id_n, 
              p.prod_price_n, 
              p.prod_num_s, 
              p.prod_description_s, 
              p.prod_status_s, 
              p.prod_brand_s, 
              p.prod_pltwid_n, 
              p.prod_pltlen_n, 
              p.prod_netwght_n, 
              p.prod_shipweight_n)

This query updates when matched. When not matched it will insert values. In the same lines, you can use DELETE, UPDATE and INSERT in the same MERGE SQL query.

The art of Public Speaking

2. Merge UPDTE , INSERT and DELETE

MERGE INTO local_product lp 
    USING product p ON (lp.prod_id_n = p.prod_id_n ) 
    WHEN MATCHED THEN UPDATE 
    SET      lp.prod_price_n = p.prod_price_n, 
             lp.prod_status_s = p.prod_status_s, 
             lp.prod_pltwid_n = p.prod_pltwid_n, 
             lp.prod_pltlen_n = p.prod_pltlen_n, 
             lp.prod_shipweight_n = p.prod_shipweight_n 
    DELETE WHERE (p.prod_status_s = 'N') 
    WHEN NOT MATCHED THEN INSERT 
    VALUES   (p.prod_id_n, 
              p.prod_price_n, 
              p.prod_num_s, 
              p.prod_description_s, 
              p.prod_status_s, 
              p.prod_brand_s, 
              p.prod_pltwid_n, 
              p.prod_pltlen_n, 
              p.prod_netwght_n, 
              p.prod_shipweight_n);