SQL – MERGE Statement

merge

admin DML Statements, Oracle , , ,

Hi Devs,
As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE.

Merge statement helps to perform insert, update and delete operations in one single statement. Merge statement was introduced in Oracle 9i with Insert and Update functionality this is the reason we call it UPSERT. There after in Oracle 10g Delete functionality added to the Merge statement but it is still known as UPSERT, the original nick name.

With the help of Merge, we can merge two tables based on a condition. Let’s have a look at the syntax and see how does it work!

Syntax:

MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,…
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,…)
values(value1,value2,…)
WHERE <insert_condition>

target_table:
This is the table in which rows will be inserted or updated or deleted.

source_table:
It can a be a select query or a table based on which source_table will get updated.

search_condition:
Oracle controller checks this condition and allow the MERGE operations either update/delete or insert.

when matched then:
When search_condition is evaluated to TRUE then Oracle database pass the control to this section and runs specified update/delete statements.

when not matched then:
When search_condition is evaluated to FALSE then Oracle database pass the control to this section and runs specified insert statement.

You must have noticed that we have WHERE clause for each DML statement. It helps us to control each operation including Insert.

We need two table with almost same data to understand the work cycle of merge statement. So, I have prepared two new tables EMP_TARGET and EMP_SOURCE.
Click Here to download the CREATE TABLE script.

TABLE – EMP_TARGET
EMPLOYEE_IDFIRST_NAMELAST_NAMESALARY
100StevenKing22000
101NeenaKochhar15000
102Lex 15000
103AlexanderHunold7000
104BruceErnst4000
TABLE – EMP_SOURCE
EMPLOYEE_IDFIRST_NAMELAST_NAMESALARY
100StevenKing23000
101NeenaKochhar16000
102LexDe Haan16000
103AlexanderHunold8000
104BruceErnst5000
105DavidAustin3800
106ValliPataballa3800
107DianaLorentz3200
108NancyGreenberg11008
109DanielFaviet8000

You can see in the above two tables that in EMP_TARGET table we have less records and missing information. Also, Salary column is not populated same as Salary column in EMP_SOURCE table. In table EMP_SOURCE, we have some extra records. We will use Merge statement to update the missing information for the existing records and insert the new records from EMP_SOURCE table.

Let’s write a merge statement with the help of above syntax.

Example:

After executing above Merge statement, you will see that both the tables have same data and same number of records.
Remember, I have not used any Where clause in the UPDATE and INSERT statements.

TABLE – EMP_TARGET
EMPLOYEE_IDFIRST_NAMELAST_NAMESALARY
100StevenKing23000
101NeenaKochhar16000
102LexDe Haan16000
103AlexanderHunold8000
104BruceErnst5000
105DavidAustin3800
106ValliPataballa3800
107DianaLorentz3200
108NancyGreenberg11008
109DanielFaviet8000
TABLE – EMP_SOURCE
EMPLOYEE_IDFIRST_NAMELAST_NAMESALARY
100StevenKing23000
101NeenaKochhar16000
102LexDe Haan16000
103AlexanderHunold8000
104BruceErnst5000
105DavidAustin3800
106ValliPataballa3800
107DianaLorentz3200
108NancyGreenberg11008
109DanielFaviet8000

Homework:
Use DELETE along with UPDATE in merge statement and INSERT with WHERE clause.
Add your code in the comment section.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

IN

Oracle – IN Condition

Hey people, In this Oracle tutorial, we will learn how to use IN condition in SQL. IN condition is used […]

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

Leave a Reply

Your email address will not be published. Required fields are marked *