DB GEEK

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:

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.

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:

MERGE INTO emp_target a USING emp_source b 
ON ( a.employee_id = b.employee_id )
WHEN MATCHED THEN 
    UPDATE SET a.first_name = b.first_name,
    a.last_name = b.last_name,
    a.salary = b.salary
WHEN NOT MATCHED THEN INSERT (
    a.employee_id,
    a.first_name,
    a.last_name,
    a.salary ) VALUES (
    b.employee_id,
    b.first_name,
    b.last_name,
    b.salary );

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.

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!