Oracle mergeinto usage

Merge is a very useful feature. It is similar to mergeinto in DB2 and insertintoonduplicatekey in Mysql. MERGEINTO

Merge is a very useful feature. It is similar to merge into in DB2 and insert into on duplicate key in Mysql. MERGE

1. Use of merge

Merge is a very useful feature. It is similar to merge into in DB2 and insert into on duplicate key in Mysql. Merge into is a new feature that appears after Oracle 9i. In simple terms, it can be a function of "Update if there is, insert if there is no.

With Merge into, you can execute 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.

2. Basic Syntax of Merge

Syntax:

1 MERGE [INTO [schema.] table [t_alias]
2 USING [schema.] {table | view | subquery} [t_alias]
3 ON (condition)
4 when matched then [merge_update_clause]
5 when not matched then [merge_insert_clause];

The common point is the following syntax.

1 merge into [Table name] [table alias]
2 USING ([query content]) [or statements like table queries]
3 ON ([conditional expression] AND [...]...)
4 when mathed then [matched update operation]
5 when not mathed then [mismatched insert operations]

For example:

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 indicates that the newStudents table is used for merge to the Students table. The matching relationship is based on the content of the Condition Clause after the on clause. Here the two table IDs are used for matching, the matching operation is the action in the when matched then clause. The action here is update set st. name = nst. name is to assign the name in newStudents to the name of Students. If no match exists, insert a statement like this. Through this simple small example, the usage of merget inot is easier to understand. Here, the merger into function, such as comparison, and then choose update or insert, such as a combination of martial arts routines. In the case of merge into, merge's performance is superior to the update/insert statements with the same functions. Some analysts say that merge into is several times more efficient than insert/update. I have never studied it well and will not comment on it.

3. improvements after 10 Gb of Oracle

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.

Oracle merge into usage and example