Oracle merge into syntax

Oracle merge into syntax

1. Usage of merge:
Merge into is a new syntax added by Oracle 9i. It is supplemented at 10 Gbit/s to merge update and INSERT statements and perform join queries with another table based on one table or subquery, match the connection conditions.

UPDATE: INSERT is performed without matching. This syntax only requires a full table scan to complete all the work. The execution efficiency is higher than simply UPDATE + INSERT, A specific application can be used for table synchronization.
2. merge into Syntax:
Syntax structure:
MERGE [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;

Syntax description:

Merge into [Table name] [alias] -- target table to be updated

USING (subquery/table name/view) [alias] -- source table

ON ([connection condition] AND [...]...) -- connection condition/update Condition

When mathed then update set [...] -- if a match is found, UPDATE the table record. If only the table record is updated, the INSERT section below can be removed.

When not mathed then insert values () [...] -- INSERT table records if they do NOT match
3. merge into Demo:
1> Create test tables and data:
-- Use the table YAG1 as the source table, and the table YAG2 as the updated target table
Create table YAG1 as select OBJECT_NAME, oOBJECT_ID FROM USER_OBJECTS where rownum <= 10;
Create table YAG2 as select OBJECT_NAME, oOBJECT_ID FROM USER_OBJECTS where rownum <= 5;
-- Modify the OBJECT_NAME of a record in Table YAG1 to create a condition that meets the UPDATE condition,
SQL> UPDATE YAG1 SET OBJECT_NAME = 'aaaaa' WHERE OBJECT_NAME = 't_ cat ';
2> Record Comparison of the first two tables updated by merge:
SQL> select a. OBJECT_ID, A. OBJECT_NAME, B. OBJECT_NAME FROM YAG1 A, YAG2 B where a. OBJECT_ID = B. OBJECT_ID (+) ORDER BY 1;
A. OBJECT_ID A. OBJECT_NAME B. OBJECT_NAME
---------------------------------------------
46366 AAAAA T_CAT
46367 SUM_STRING
46368 ARRAYLIST
46369 TYSKZ_SJDX
46370 TYSKZ_SJXMGX

46371 PARAOBJECT
46372 T_LINK
46373 STR_SPLIT
46374 SPLIT_TYPE
46375 SYS_PLSQL_95487_9_1
3> execute the following merge into statement:
Merge into YAG2
USING YAG1 B
ON (A. OBJECT_ID = B. OBJECT_ID)
WHEN MATCHED THEN
Update set a. OBJECT_NAME = B. OBJECT_NAME
WHEN NOT MATCHED THEN
Insert values (B. OBJECT_NAME, B. OBJECT_ID );
COMMIT;
4> Record Comparison of the last two tables after merge into update:
SQL> select a. OBJECT_ID, A. OBJECT_NAME, B. OBJECT_NAME FROM YAG1 A, YAG2 B where a. OBJECT_ID = B. OBJECT_ID (+) ORDER BY 1;
A. OBJECT_ID A. OBJECT_NAME B. OBJECT_NAME
---------------------------------------------
46366 AAAAA
46367 SUM_STRING
46368 ARRAYLIST
46369 TYSKZ_SJDX
46370 TYSKZ_SJXMGX
46371 PARAOBJECT
46372 T_LINK
46373 STR_SPLIT
46374 SPLIT_TYPE
46375 SYS_PLSQL_95487_9_1 SYS_PLSQL_95487_9_1

Installing Oracle 12C in Linux-6-64

RHEL6.4 _ 64 install a single instance Oracle 12cR1

New Features of Oracle 12C: Paging Query

12 new features of Oracle 12C