Trending September 2023 # How Merge Statement Work In Oracle # Suggested October 2023 # Top 13 Popular

You are reading the article How Merge Statement Work In Oracle updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How Merge Statement Work In Oracle

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Below is the syntax:

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.

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:

);

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:

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:

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:

);

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.