Oracle's Merge into usage

1.merge into uses

Merge is a very useful feature, almost the same as the merge into function in DB2, similar to the INSERT into on duplicate key in MySQL. MERGE into is a new feature that appears only after Oracle 9i. In a nutshell, it can be a feature of "have updates, no inserts."

Merge into enables you to perform inserts and updates operations on a table in one SQL statement at the same time. 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.

Basic syntax for 2.Merge into

The syntax is as follows:

1MERGE[into [schema.] Table [T_alias] 2USING[schema.]{Table | View |subquery}[T_alias] 3  on(condition)4  whenMatched Then[Merge_update_clause]5  when  notMatched Then[Merge_insert_clause];

The popular point is the following syntax

1MERGE into [Table name] [Table Aliases] 2USING ([Query Content])[or a statement like a table query] 3  on([Conditional Expressions]  and [...]...) 4  whenMathed Then [Matching update operations] 5  when  notMathed Then [Do not match the insert Operation] 

Let's take a look at:

 1  merge into  Students St using newstudents nst on  (st.id =   Nst.id)  2  when  matched then  3  update  set  st.name  Nst.name  4  when  not  matched "then  5  insert  values  (nst.id, Nst.name, Nst.sex) 

The previous merger into Students using newstudents means the merge to the Students table with the Newstudents table, and the matching relationship is based on the contents of the conditional clause following on, which is based on the IDs of the two tables. So the match on our operation is when the matched then clause in the action, where the action is the update set St.name =nst.name, is the name of Newstudents, assigned to the name of students. If there is no match on the insert such a statement goes in. With this simple little example, the use of this merget inot is relatively easy to understand. Here merger into functions, like comparisons, and then choose to update or INSERT, such as a set of martial arts routines of a combination of boxing. When doing merge into, this same situation, the performance of the merge is superior to the equivalent function of the Update/insert statement. There is analysis that merge into is a simple insert/update efficiency of several times, I have not been refined, do not comment.

3.Oracle 10g post-improvement

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

Oracle's Merge into usage