How to Use merge into-are you using merge?

 

Merge is a very useful function, similar to the insert into on duplicate key in MySQL.

Oracle introduced the merge command in 9i,
With this merge, You can execute the inserts and updates operations on a table in an SQL statement at the same time. of course, whether it is update or insert is determined based on your specified conditions. merge into can update table a data using Table B. If table A does not have data, insert the data in Table B into Table. the merge command selects rows from one or more data sources to update or inserting to one or more tables.

Syntax:
Merge into [your table-name] [rename your table here]
Using ([write your query here]) [rename your query-SQL and using just like a table]
On ([conditional expression here] and [...]...)
When mathed then [here you can execute some update SQL or something else]
When not mathed then [execute something else here! ]

Let's take a look at a simple example to introduce the usage of a merge.
Merge into products P using newproducts Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name
When not matched then
Insert values (NP. product_id, NP. product_name, NP. Category)

In this example. In the previous expression of merger into products using newproducts, The newproducts table is used for merge to the products table. The matching relationship of merge is the content of the Condition Clause after on, the matching is performed based on the product_id of the two tables. The matching operation is the action in the when matched then clause. The action here is update set p. product_name = NP. product_name: it is clear that the content in newproduct is assigned to product_name of product. If no match exists, insert a statement like this. Let's see if the usage of this merget inot is clear at a glance. Here, the merger function is like a comparison, and then an update or insertion is a series of combination boxing. In the same case when doing merge, the performance of merge is superior to the update/insert statements with the same functions. Some people once analyzed that merge has contributed a lot to the performance of batch processing. I personally think this is not the case.

We can also use a view or subquery after using. For example, we replace newproducts
Merge into products P using (select * From newproducts) Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name
When not matched then
Insert values (NP. product_id, NP. product_name, NP. Category)
Yes.

In Oracle 10g, merge has the following improvements:
1. The update or insert clause is optional.
2. You can add a where clause to the update and insert clauses.
3. Use the constant filter predicate in the on condition to insert all rows to the target table. You do not need to connect the source table to the target table.
4. The update clause can be followed by the delete clause to remove unnecessary rows.

Let's take a look at the new features one by one through examples.

1. The update or insert clause is optional.
In 9i, because both insert into and update must exist, it means update is insert. It does not support a single operation. Although it can save the country by curve, it is too strong. 10 Gb is optional and can meet our more needs.
For example, the above sentence
We can only have update or insert
Merge into products P using newproducts Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name
Here, if the match is correct, it will be updated. If it does not exist, it will not matter.

2. You can add a where clause to the update and insert clauses.
This is also a functional improvement that can meet our more needs. The where function is obviously a filter condition, and we add some additional conditions, update and insert operations that only meet the where Condition
Merge into products P using (select * From newproducts) Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name where NP. product_name like 'ol %'
Here, we only update the matching where product_name starts with 'ol '. If it does not start with 'ol', it does not do anything. You can also add where in insert.
For example
Merge into products P using (select * From newproducts) Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name where NP. product_name like 'ol %'
When not matched then
Insert values (NP. product_id, NP. product_name, NP. Category) where NP. product_name like 'ol %'

The number of returned results is different.

3. Use the constant filter predicate in the on condition to insert all rows to the target table. You do not need to connect the source table to the target table.

Merge into products P using (select * From newproducts) Np on (1 = 0)
When matched then
Update Set P. product_name = NP. product_name
When not matched then
Insert values (NP. product_id, NP. product_name, NP. Category)
I personally think this function does not make much sense. Our insert into itself supports this function and there is no need to use merge.

4. The update clause can be followed by the delete clause to remove unnecessary rows.
Delete can only work with update to delete records of clauses that meet the where condition.
Merge into products P using (select * From newproducts) Np on (P. product_id = NP. product_id)
When matched then
Update Set P. product_name = NP. product_name Delete where P. product_id = NP. product_id where NP. product_name like 'ol %'
When not matched then
Insert values (NP. product_id, NP. product_name, NP. Category)
The purpose is to update the prodcut_name of the matched record to the product, and delete the product whose name starts with "ol.

Merge into is also a DML statement. Like other DML statements, you need to end the transaction through rollback and commit.

Merge is a very powerful feature that is often used in our needs, so we must learn it well.

The required test script is provided for download in the attachment.
Merge into sample. SQL