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_ID FIRST_NAME LAST_NAME SALARY
100 Steven King 22000
101 Neena Kochhar 15000
102 Lex   15000
103 Alexander Hunold 7000
104 Bruce Ernst 4000
TABLE – EMP_SOURCE
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
100 Steven King 23000
101 Neena Kochhar 16000
102 Lex De Haan 16000
103 Alexander Hunold 8000
104 Bruce Ernst 5000
105 David Austin 3800
106 Valli Pataballa 3800
107 Diana Lorentz 3200
108 Nancy Greenberg 11008
109 Daniel Faviet 8000

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_ID FIRST_NAME LAST_NAME SALARY
100 Steven King 23000
101 Neena Kochhar 16000
102 Lex De Haan 16000
103 Alexander Hunold 8000
104 Bruce Ernst 5000
105 David Austin 3800
106 Valli Pataballa 3800
107 Diana Lorentz 3200
108 Nancy Greenberg 11008
109 Daniel Faviet 8000
TABLE – EMP_SOURCE
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
100 Steven King 23000
101 Neena Kochhar 16000
102 Lex De Haan 16000
103 Alexander Hunold 8000
104 Bruce Ernst 5000
105 David Austin 3800
106 Valli Pataballa 3800
107 Diana Lorentz 3200
108 Nancy Greenberg 11008
109 Daniel Faviet 8000

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..

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Translate

Oracle – Translate Function

Hi Geeks, Today, we will talk about an Oracle’s inbuilt function TRANSLATE. TRANSLATE works same as REPLACE function does but […]

Leave a Reply

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