Use of the merge statement in Oracle

Oracle introduced the merge command in 9i, which enables you to perform inserts and updates operations on a table in one SQL statement. Of course update or insert is based on your specified conditions, Merge into can be used to update the A table with the B table, if not in a table, then the B table data into a table. The merge command selects rows from one or more data sources to updating or inserting to one or more tables.

The syntax is as follows
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 isn't mathed then [execute something else here!]

Let's take a look at a simple example to introduce the use of a merge into
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 isn't matched then insert values (np.product_id, Np.product_name, Np.category)

In this case. The previous merger into product using NewProducts represents the merge to the Products table using the NewProducts table, and the merge matching relationship is the content of the conditional clause on the back of the, here according to the two table _id to match, then the match on our operation is when the matched then clause in the action, here the action is update set p.product_name = Np.product_name, It is obvious that the contents of Newproduct are assigned to the product_name of product. If there is no match on the insert such a statement goes in. Let's see if the usage of this merget inot is at a glance. Here merger function, like comparison, and then choose to update or INSERT, is a series of combination of boxing, when doing the merge, so the same situation, the performance of the merge is better than the equivalent function of the Update/insert statement. Someone once analyzed the merge is the bulk processing to the performance contribution is very big, personally thinks this is not textual research.

We can also use views or subqueries behind the using. For example, we replace the 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 isn't matched then insert values (np.product_id, Np.product_name, Np.category)
is also possible.

The merge in Oracle 10g has some of the following improvements:
1. Update or INSERT clauses are optional
2. Update and INSERT clauses can be added to the WHERE clause
3. Use the constant filter verb in the on condition to insert all rows into the target table without connecting the source and target tables
4, the UPDATE clause can be followed by a delete clause to remove some unwanted rows

Let's take a look at the new features on the one by one example

1. The UPDATE or INSERT clause is optional
In 9i because must insert into and update to exist, that is, not update is insert, does not support a single operation, although still can curve salvation, hehe but some too strong. And 10g is optional, to meet more of our needs, such as the above sentence we can only exist 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 updated, it doesn't exist.

2. Update and INSERT clauses can be added to the WHERE clause
This is also a functional improvement, to meet our more needs, this where role is obviously a filter condition, is we add some additional conditions,
For updates and inserts that only meet the Where condition
Merge into products p using (SELECT * from NewProducts) np on (p.product_id = np.product_id)
When matched and update set p.product_name = Np.product_name where np.product_name like ' ol% '

Here is only for the product_name start is ' ol ' match on the update, if the beginning is not ' ol ' is the match and do not do what things,
Insert can also include where, for example
Merge into products p using (SELECT * from NewProducts) np on (p.product_id = np.product_id)
When matched and update set p.product_name = Np.product_name where np.product_name like ' ol% '
When isn't matched then inserts values (np.product_id, Np.product_name, np.category) where np.product_name like ' ol% '
Note here that the number of result rows they return is different.

3. Use the constant filter verb in the on condition to insert all rows into the target table without connecting the source and target tables
Merge into products p using (SELECT * from NewProducts) NP on (1=0)
When matched then update set p.product_name = Np.product_name
When isn't matched then insert values (np.product_id, Np.product_name, Np.category)
Personally feel that this function does not make much sense, our insert into itself supports such a function, there is no need to use the merge

4. After the UPDATE clause, you can follow the DELETE clause to remove some unwanted rows
Delete can only mate with update to delete the record of the clause that satisfies 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 isn't matched then insert values (np.product_id, Np.product_name, Np.category)
The goal here is to update the prodcut_name of the matching records to product, and to remove the product_name beginning with the ol.

Merge into is also a DML statement that, like any other DML statement, needs to end the transaction through rollback and commit.
Merge is a very powerful feature, and is often used in our needs a useful function, so we must learn well.

MERGE/*+ Parallel (t 2) */into T-tmp1 USING T1 TMP2 on (tmp1.id=tmp2.id)
When matched then
UPDATE SET tmp1.object_name = tmp2.object_name
When isn't matched then
INSERT VALUES (tmp2.id,tmp2.object_name);