How does MERGE Statement works in Oracle ?

MERGE was included in the Oracle 9i version and above. MERGE statement is used when the user wants to update and insert some values into a table based on selecting the values from another table. This statement allows user to use join to two different statements into one statement instead of going for multiple inserts/updates statements.

MERGE is a deterministic statement, a row which is updated cannot be updated again in the same MERGE statement. Prerequisites for this is that user should have INSERT and UPDATE table/view privileges on the target table and SELECT privilege on the source table.

INTO Clause:
INTO clause tells the target table which user wants to update and insert.

USING Clause:
USING clause is to specify the source of the data, which will be updated or inserted in the target table. The source can be a table, view, or the result of a sub-query.

ON Clause:
ON clause is to specify the condition based on which the MERGE statement will either
updates or inserts records in the target table. For each row in the target table for which the search condition is true, corresponding source table data will be updated.
If the condition is not true for any rows, then corresponding source table data will be inserted into the target table.

WHEN MATCHED | NOT MATCHED:
These clauses tells Oracle how to respond to the results of the join condition in the ON clause. Oracle performs update on the target table if the condition of the ON clause is true. When the update clause is executed on the target table, then all update triggers defined on that table will also gets executed.

Points to ponder while updating a View:

• You cannot specify DEFAULT when updating a view.

• You cannot update a column that is referenced in the ON condition clause.
  

Oracle performs insert on the target table if the condition of the ON clause is false. When the insert clause is executed on the target table, then all insert triggers defined on that table will also gets executed.

Following example will make it more clear:

We have an employee table with few records:

Select * from emp;

Emp_id Salary

******* ******
1 100
2 200
3 300
4 400
5 500

We will create our new target table and will populate with emp table data.

Create table t1(emp_id number, salary number);

Insert into t1 select * from emp;

Commit;

Lets assume after some time few more records added to emp table.

Select * from emp;

Emp_id Salary
****** ******
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800

Before Oracle 9i, if we need to update the salary of the existing employees with 20% hike with the data of the new joined employees also from emp table into our target table t1.

Update
(Select a.emp_id empl_id, a.salary sal from t1 a, emp b where a.emp_id = b.emp_id) x
set sal = sal*.20;

Insert into t1 (emp_id, salary)
select emp_id, salary from emp where emp_id not in (select emp_id from t1);

After using MERGE statement this can be done in single statement which is as follows:

MERGE INTO t1 a
USING emp b
ON (a.emp_id = b.emp_id)
WHEN MATCHED THEN UPDATE SET salary = a.salary*.20
WHEN NOT MATCHED THEN INSERT (a.emp_id,a.salary)
VALUES(b.emp_id,b.salary);

In addition to this, In Oracle 10g we can use optional DELETE clause also in MERGE statement. But, there are following few constraints:

• DELETE clause cannot be used independently in MERGE statement. It has to be embedded with UPDATE or INSERT statement.

• DELETE clause will work only on the rows which are filtered based on the join condition mentioned in ON clause.

• DELETE clause will affect only those rows which are updated in MERGE statement.

for e.g if user wants to delete the records where still the salary is less then 2000 after update.

MERGE INTO t1 a
USING emp b
ON (a.emp_id = b.emp_id)
WHEN MATCHED THEN
UPDATE SET a.salary = b.salary*.20
DELETE WHERE (a.salary < 2000)
WHEN NOT MATCHED THEN INSERT (a.emp_id,a.salary)
VALUES(b.emp_id,b.salary);

In this, first UPDATE clause will update the matched rows with 20% increase in salary and then updated employees with less then 2000 salary will be deleted from the target table.