The difference between Merge and Update

What is the difference between Merge and Update in SQL?

Comments for The difference between Merge and Update

Average Rating starstarstarstarstar

Click here to add your own comments

Oct 08, 2015
Rating
starstarstarstarstar
Good post
by: Mr. Thora Hirthe

Thanks for your brilliant articles! Many thanks for answering!

May 07, 2015
Rating
starstarstarstarstar
SQL merge and update
by: Anonymous

The basic difference between merge and update in SQL is that with a merge statement you can optionally use it to insert new rows into and/or delete existing rows from a table whereas with an update statement you can only update existing records. Of course merge statements can also be used to update existing rows.

The effect of the merge statement is essentially updating matching records and optionally insert non-matching records. Update merely updates all the rows that pass through the optional filter (the where clause). Therefore merge can be regarded as an upsert command and is primarily used in data warehouse applications to load the day's tranasactions (such as sales) from the transaction-processing system into the history table (such as sales history) where a large volume of data is being moved.

Let's look at an example - we'll stick with sales and sales history but just specify the the bare bones for teh tables to keep things simple.

First we'll create the tables and put some data in them.

CREATE TABLE sales
(inv_id number primary key
,cust_id number
,sales_amt number
,prod_id number);
INSERT INTO SALES VALUES (1,1,1200,1);
INSERT INTO SALES VALUES (2,1,912,1);
INSERT INTO SALES VALUES (3,1,820,1);
INSERT INTO SALES VALUES (4,1,200,1);
INSERT INTO SALES VALUES (5,1,4200,1);
INSERT INTO SALES VALUES (6,1,12300,1);
INSERT INTO SALES VALUES (7,1,1400,1);

CREATE TABLE sales_history
(inv_id number primary_key
,cust_id number
,sales_amt number
,prod_id number);

INSERT INTO sales_history VALUES (1,1,200,1);
INSERT INTO sales_history VALUES (2,1,912,1);
INSERT INTO sales_history VALUES (3,1,20,1);


Next we merge sales and sales_history.

MERGE INTO sales_history sh

USING
(SELECT inv_id
,cust_id
,sales_amt
,prod_id
FROM sales) s
ON (s.inv_id = sh.inv_id)
WHEN MATCHED THEN UPDATE SET
sh.sales_amt=s.sales_amt DELETE WHERE sh.sales_amt<100
WHEN NOT MATCHED THEN INSERT (sh.inv_id,sh.cust_id,sh.sales_amt,sh.prod_id) VALUES (s.inv_id,s.cust_id,s.sales_amt,s.prod_id);


Finally, let's query the sales_history table to see the results. (Scroll down for results).
SELECT * FROM sales_history;















































INV_ID CUST_ID SALES_AMT PROD_ID
1112001
219121
318201
412001
5142001
61123001
7114001

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.