ORACLE MERGE example


Merge is a new powerful syntax in Oracle’s world. Let me give you an example;

The merge syntax is like below;

MERGE INTO
Destination Table_Name
USING
Source Table_Name
ON
Condition
WHEN MATCHED THEN
Operation //–>do not use ‘;’ here otherwise you got unexpected results
WHEN NOT MATCHED THEN
Operation

Let me explain it with an example;
We have got two tables ; Customers , Insurances

We would like to learn if a customer has an insurance record in insurance table, and if there is then we would like to add more insurance years for the customers (We will use UPDATE clause here). On the other hand, if the customer does not have an insurance we would like to give a new insurance record for these customers. (We will use INSERT clause here)

This resultset belongs to customers table;
CUSTOMER_ID            NAME                 SURNAME
———————- ——————– ——————–
1                                      tuna                          tore
2                                      guru                           gurume
3                                      mike                               long
4                                      not have insurance   give me insurance

4 rows selected

Here Insurance table;

INSURACEID             VALIDYEARS             CUSTOMERID
———————- ———————- ———————-
1                                         1                      1
2                                         3                      2
3                                         4                      3

3 rows selected

From insurance table we can conclude that, customer having with customer_id = 1 has 1 year insurance valid period.
customer having with customer_id = 2 has 3 years valid perios and so on..

With the help of merge sql, we need to add 2 more years for customers having an existing insurance period in insurances table and if a customer does not have insurance then we need to start an insurance for him/her.

Here is merge;

MERGE INTO INSURANCES i
USING
(SELECT * FROM CUSTOMERS) c
ON
(c.customer_id = i.customerid)
WHEN MATCHED THEN
UPDATE SET i.validyears = i.validyears + 2
WHEN NOT MATCHED THEN
INSERT (i.insuraceid,i.validyears,i.customerid)
VALUES (INSURANCE_SEQ.nextval,0,c.customer_id);

Be careful on INSERT syntax which is different compared to regular INSERT INTO syntax. (There is no INTO here and the table name).

And the result is like that;

INSURACEID             VALIDYEARS             CUSTOMERID
———————- ———————- ———————-
1                                             3                      1
2                                             5                      2
3                                             6                      3
4                                             0                      4

4 rows selected

Here a customer with an customerid=4 is added to insurance table and 2 more years is added for all customers who has an existing record in insurances table.
Thanks.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s