A probe into Oracle MERGE INTO Grammar
1. The purpose of MERGE INTO:
MERGE INTO is a new syntax of Oracle 9i, which is added at 10g to merge UPDATE and INSERT statements. Join queries are made according to one table or subquery with another table, and the join conditions are matched.
UPDATE, mismatch to INSERT, this syntax only needs a full table scan to complete all the work, the execution efficiency will be higher than the simple UPDATE+INSERT, the specific application can be used for synchronization between tables.
2. Syntax of MERGE INTO:
Grammatical 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]-- the target table that needs to be updated
USING (subquery / table name / view) [alias]-source table
ON ([connection condition] AND [...]...)-- join condition / update condition
WHEN MATHED THEN UPDATE SET [...]-if there is a match, update the table record. If it is only updated, the following INSERT section can be removed.
WHEN NOT MATHED THEN INSERT VALUES () [...]-if there is no match, insert the table record
3. MERGE INTO demonstration:
1 > create test tables and data:
-- Table YAG1 as the source table and table YAG2 as the target table for updates
Record comparison of the first two tables updated by CREATE TABLE YAG1 AS SELECT OBJECT_NAME,oOBJECT_ID FROM USER_OBJECTS WHERE ROWNUMMERGE INTO:
SQL > SELECT A. OBJECTT ORDER BY ORDER BY A. OBJECTCTNAME FROM YAG1 AreYAG2B WHERE A.OBJECT_ID=B.OBJECT_ID (+) IDMagne1
A.OBJECT_ID A.OBJECT_NAME B.OBJECT_NAME
46366 AAAAA T_CAT
46367 SUM_STRING SUM_STRING
46368 ARRAYLIST ARRAYLIST
46369 TYSKZ_SJDX TYSKZ_SJDX
46370 TYSKZ_SJXMGX 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 A
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 > comparison of the records of the two tables after MERGE INTO update:
SQL > SELECT A. OBJECTT ORDER BY ORDER BY A. OBJECTCTNAME FROM YAG1 AreYAG2B WHERE A.OBJECT_ID=B.OBJECT_ID (+) IDMagne1
A.OBJECT_ID A.OBJECT_NAME B.OBJECT_NAME
46366 AAAAA AAAAA
46367 SUM_STRING SUM_STRING
46368 ARRAYLIST ARRAYLIST
46369 TYSKZ_SJDX TYSKZ_SJDX
46370 TYSKZ_SJXMGX TYSKZ_SJXMGX
46371 PARAOBJECT PARAOBJECT
46372 T_LINK T_LINK
46373 STR_SPLIT STR_SPLIT
46374 SPLIT_TYPE SPLIT_TYPE
46375 SYS_PLSQL_95487_9_1 SYS_PLSQL_95487_9_1
.
