MERGE SQL Query These are Useful Points to Know

Free images: Unsplash

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 SQL query with 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 SQL query with UPADTE , 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);

Here when matched it will update the rows. It will DELETE when the given condition matches. When not matched it will insert rows.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.