The merge syntax after oracle9i __oracle

The

Merge statement is oracle9i new syntax for merging update and INSERT statements. Through the merge statement, according to a table or the join conditions of a subquery to query the other table, join condition matching for update, unable to match the execution of the insert. This syntax only needs a full table scan to complete all the work, execution efficiency is higher than insert+update.

MERGE into table_name as Table_alias
USING (Table | view | sub_query) as Alias
On (Join condition)
When matched THEN
UPDATE SET
col1 = Col_val1,
col2 = Col2_val
When not matched THEN
INSERT (column_list)
VALUES (column_values);
Still is
MERGE into table_name as Table_alias
USING (Table | view | sub_query) as Alias
On (Join condition)
When matched THEN
UPDATE table_name SET
col1 = Col_val1,
col2 = Col2_val
When not matched THEN
INSERT (column_list)
VALUES (column_values);

Here's a concrete example: http://blog.itpub.net/post/468/14844 CREATE TABLE T as SELECT rownum ID, a. * from Dba_objects A;

Table has been created. CREATE TABLE T1 as
SELECT rownum ID, OWNER, TABLE_NAME, CAST (' TABLE ' as VARCHAR2 ()) object_type
From Dba_tables;

Table has been created.
MERGE into T1 USING T
On (T.owner = T1. OWNER and T. object_name = T1. table_name and T.object_type = T1. object_type)
When matched THEN UPDATE SET t1.id = t.id
When not matched THEN INSERT VALUES (t.id, T.owner, T. object_name, T.object_type);

The

6165 rows have been merged. Select  ID, OWNER,  object_name, object_type  from  t
 minus
  Select   *   from  T1;

The

Unselected rows
Merge syntax is really simple, but here's a little bit of an example.
sql> DROP TABLE T;
The table has been discarded.
sql> DROP TABLE T1;
The table has been discarded.
sql> CREATE TABLE T as SELECT rownum ID, a.* from Dba_objects A;
The table has been created.
sql> CREATE TABLE T1 as SELECT rownum ID, OWNER, table_name from Dba_tables;
The table has been created.
sql> MERGE into T1 USING T
2 on (T.owner = T1. OWNER and t.object_name = T1. TABLE_NAME)
3 when matched THEN UPDATE SET t1.id = t.id
4 If not matched THEN INSERT VALUES (t.id, T.owner, T.obje Ct_name);
MERGE into T1 using T
*
error is on line 1th:
ORA-30926: Unable to get a stable set of rows in the source table
This error is the most common error in using MERGE. The reason for this error is that the record of T is not unique because of the connection condition. The simplest workaround is similar to this:
sql> MERGE into T1
2 USING (SELECT OWNER, object_name, MAX (ID) IDs from T GROUP by OWNER, object_name T
3 on (T.owner = T1.) OWNER and t.object_name = T1. TABLE_NAME)
4 when matched THEN UPDATE SET t1.id = t.id
5 If not matched THEN INSERT VALUES (t.id, T.owner, T.obje Ct_name); The
5775 rows have been merged.
In addition, the update of the merge statement cannot modify the columns used for the connection, otherwise it will be an error, as the details can refer to:

===============================================================

ref:http://tomszrp.itpub.net/post/11835/263865

Prior to Oracle 10g, the merge statement supported 2 simple usages of matching updates and mismatched inserts, and Oracle enhanced the merge statement in 10g, adding conditional options and delete operations. Below I have a demo to briefly introduce the enhanced 10g merge and 10g before the use of the merge.

　

Referring to Oracle's SQL Reference, you can see the syntax for the merge statement as follows:
MERGE [hint] into [schema.] table [T_alias] USING [schema.]
{Table | view | subquery} [T_alias] On (condition)
When matched THEN Merge_update_clause
When not matched THEN merge_insert_clause;

Below I do a test on the Windows XP 10.2.0.1 version to see