Updated March 3, 2023
Introduction to Oracle MERGE
An Oracle MERGE statement is used to pull data from the source table(s) and update or insert into the target table based on condition. Merge statement allows us to make condition-based insert or update into a target table. It is introduced in Oracle 9i version and it supports 9i or later version. It is a DML statement. It performs data manipulation operation on the table(s). This is a very useful function for pulling data from the source table and updating or inserting or deleting into a target table in a single statement using condition. Merge operation is also known as the UPSERT operation. In the same Merge statement, the same row cannot be updated multiple times. Users must have Insert / Update / Delete object privilege to perform Merge operation. This is a very convenient statement to combine multiple operations. Multiple DML statements can be avoided by using of Merge statement.
Syntax
Below is the syntax:
MERGE INTO TargetTable
USING SourceTable
ON Condition
WHEN MATCHED THEN
UPDATE SET col_1 = value_1, col_2 = value_2...col_n = value_n
WHERE <UpdateCondition>
[DELETE WHERE <DeleteCondition>]
WHEN NOT MATCHED THEN
INSERT (col_1,col_2...col_n)
Values(value_1,value_2...value_n)
WHERE <InsertCondition>;
Explanation:
col_1, col_2…col_n: This is the column name that will be the part of the Merge operation.
value_1, value_2…value_n: Values that will be used for DML operation.
TargetTable: Merge operation will be operated for DML operation on the table.
SourceTable: Table that will be used for pulling data.
Condition: Condition that will be used for the Merge operation.
UpdateCondition: This is specifically used for the update operation. Update operation performs on this condition.
DeleteCondition: This is specifically used for Delete operation. Delete operation performs on this condition.
InsertCondition: This is specifically used for the Insert operation. Insert operation performs on this condition.
Matched / Not Matched: This is used to indicate when DML operation should take place. Matched tells to start specified DML operation when SourceTable data or conditional data is matching with TargetTable. And Not Matched is just opposite to Matched.
How does MERGE statement work in Oracle?
The MERGE statement is a key technique to perform DML operation (Insert/ Update/ Delete) in a single statement. Basically Merge statement takes Data from SourceTable based on condition and performs DML operation on specified condition in ON clause. It reduces multiple DML statements.
Examples to Implement Oracle MERGE
In this section, we’ll see the implementation of Oracle MERGE Statement and its behavior. For that, we will use the below sample table (Employee&Employee1) with 14& 3records to understand the Oracle MERGE Statement behavior.
Code:
SELECT * FROM Employee;
Output:
Code:
SELECT * FROM Employee1;
Output:
1. Oracle MERGEStatement WHEN NOT MATCHED condition
In the above tables Employee and Employee1 consisting of 14 and 3 records. So in this example, we’ll insert the records which are not present in table Employee1.
Code:
MERGE INTO EMPLOYEE1 M USING
(SELECT * FROM Employee) LM ON (LM.Id=M.Id)
WHEN NOT MATCHED THEN
INSERT
(
M.ID,
M.name,
M.designation,
M.manager_id,
M.doj,
M.salary,
M.bonus,
M.deptnumber
)
VALUES
(
LM.ID,
LM.name,
LM.designation,
LM.manager_id,
LM.doj,
LM.salary,
LM.bonus,
LM.deptnumber
);
Output:
Explanation: In the above output showing 11 rows merged. In the above example, Employee is the source table of where the merge statement pulls the data and Employee1 is the target table where the merge statement inserts the data based on the ON clause condition that is the ID from both the tables. So while merging the data merge statement checks the condition that ID of source data is matching or not with the ID of target data if it’s not matching then the merge statement inserts the data as a new record. So before this merge statement, Employee1 was holding 3 records and now Employee table consisting of 14 records.
Code:
SELECT * FROM Employee1;
Output:
2. Oracle MERGE Statement WHEN MATCHED condition
In this example, we’ll see WHEN MATCHED condition works. Rollback the changes in Employee1 table and it consisting only three rows.
Code:
SELECT * FROM Employee1;
Output:
Now we will use the MERGE statement to update Deptnumber of this table.
Code:
MERGE INTO EMPLOYEE1 M USING
(SELECT * FROM Employee) LM ON (LM.Id=M.Id)
WHEN MATCHED THEN
UPDATE SET M.deptnumber=40;
Output:
Explanation: In the above output showing 3 rows merged. In the above example, Employee is the source table, from where the merge statement pulls the data and Employee1 is the target table where the merge statement Update the data based on ON clause condition that is the ID from both the tables. So while merging the data, the merge statement checks the condition that ID of source data is matching or not with the ID of target data if it’s matching then the merge statement Update the matched row(s). So before this merge statement, Employee1 was holding 3 records with Deptnumber 10, and the merge statement updated the Deptnumber 10 to 40.
Code:
SELECT * FROM Employee1;
Output:
3. Oracle MERGE Statement
In this example, we’ll use altogether, and Merge statement will perform together easily.
Here target table is Employee1 with initial 3 records.
Code;
SELECT * FROM Employee1;
Output:
Code:
MERGE INTO EMPLOYEE1 M USING
(SELECT * FROM Employee) LM ON (LM.Id=M.Id)
WHEN MATCHED THEN
UPDATE SET M.Deptnumber=40
DELETE WHERE (LM.Salary>25000)
WHEN NOT MATCHED THEN
INSERT
(
M.ID,
M.name,
M.designation,
M.manager_id,
M.doj,
M.salary,
M.bonus,
M.deptnumber
)
VALUES
(
LM.ID,
LM.name,
LM.designation,
LM.manager_id,
LM.doj,
LM.salary,
LM.bonus,
LM.deptnumber
);
Output:
Explanation: In the above Merge statement all three DML operations (Insert, Update, and Delete) get performed on the target table (Employee1). And output returns 13 records. Employee1 table was consisting of 3 records with debt number 10.
1. Update operation updates the Deptnumber from 10 to 40 because all 3 records are matched with source table
2. Delete operation deletes the records if the salary is higher than 25000 and it finds one record out three. So it gets deleted and after that remain only two records (yellow highlighted).
3. Insert operation inserts all source table (Employee) data into the target table which are not matching the ON clause condition. So the final output showing 13 records.
TIPS: Default value cannot be specified while updating a VIEW.
Conclusion
Oracle MERGE statement is very useful to perform DML operations in a single statement. It reduces multiple DML statements. It is useful to copy data from the source table to the target table which is/are not existing in the target table. Merge is a convenient way to combine multiple DML operations.
Recommended Articles
This is a guide to Oracle MERGE. Here we discuss an introduction to Oracle MERGE, syntax, how does it work, examples for better understanding. You can also go through our other related articles to learn more –
