Turn: Use of merge into in Oracle, oraclemerge

Turn: Use of merge into in Oracle, oraclemerge

Recently, Oracle's Merge was used in the project.

This command uses a statement to update and insert data to a table from one or more data sources. in ORACLE 9i, the UPDATE and INSERT keywords must be specified at the same time. ORACLE 10g has made the following changes.

Features:

1. insert and update are optional;

2. UPDATE and INSERT can be followed by the WHERE clause;

3. You can use constants in the on condition to insert all rows to the target table without connecting to the source and target tables;

4. The UPDATE clause can be followed by delete to remove unnecessary rows.

Example:

1 create table PRODUCTS 2 (3 PRODUCT_ID INTEGER, 4 PRODUCT_NAME VARCHAR2 (60), 5 CATEGORY VARCHAR2 (60) 6); 7 8 insert into PRODUCTS values (1501, 'vivitar 100 ', 'Using dns'); 9 insert into PRODUCTS values (1502, 'olympus is50', 'using dns'); 10 insert into PRODUCTS values (1600, 'play gym ', 'toys '); 11 insert into PRODUCTS values (1601, 'lamaze', 'toys'); 12 insert into PRODUCTS values (1666, 'ha Rry potter ', 'dvd'); 13 commit; 14 15 create table NEWPRODUCTS 16 (17 PRODUCT_ID INTEGER, 18 PRODUCT_NAME VARCHAR2 (60), 19 CATEGORY VARCHAR2 (60) 20 ); 21 22 insert into NEWPRODUCTS values (1502, 'olympus CAMERA ', 'using NCS'); 23 insert into NEWPRODUCTS values (1601, 'lamaze', 'toys '); 24 insert into NEWPRODUCTS values (1666, 'Harry POTTER ', 'toys'); 25 insert into NEWPRODUCTS values (1700, 'Wait INTERFACE ', 'books'); 26 commit; 27 1, omitted update or insert 28 merge into products p 29 2 USING newproducts np 30 3 ON (p. product_id = np. product_id) 31 4 when matched then 32 5 UPDATE 33 6 SET p. product_name = np. product_name, 34 7 p. category = np. category; 35 Use the product_name and category fields in the table newproducts to update the product_name and category of the same product_id In the table products. 36 37 2. when the conditions are not met, INSERT the data in the newproducts table into the table produ. Cts. 38 39 merge into products p 40 USING newproducts np 41 ON (p. product_id = np. product_id) 42 when not matched then 43 INSERT 44 VALUES (np. product_id, np. product_name, 45 np. category); 46 3, conditional insert and update 47 48 merge into products p 49 USING newproducts np 50 ON (p. product_id = np. product_id) 51 when matched then 52 UPDATE 53 SET p. product_name = np. product_name 54 WHERE p. category = np. categor Y; 55 insert and update both have the where clause 56 57 58 59 merge into products p 60 USING newproducts np 61 ON (p. product_id = np. product_id) 62 when matched then 63 UPDATE 64 SET p. product_name = np. product_name, 65 p. category = np. category 66 WHERE p. category = 'dve' 67 when not matched then 68 INSERT 69 VALUES (np. product_id, np. product_name, np. category) 70 WHERE np. category! = 'Books '71 4, unconditional insert 72 73 merge into products p 74 USING newproducts np 75 ON (1 = 0) 76 when not matched then 77 INSERT 78 VALUES (np. product_id, np. product_name, np. category) 79 WHERE np. category = 'books '80 5, delete Clause 81 82 1 merge into products p 83 2 using newproducts np 84 3 on (p. product_id = np. product_id) 85 4 when matched then 86 5 update 87 6 set p. product_name = np. product_name 88 7 delete where category = 'macle1 _ cate '; 89 90 select * 91 92 from products; 93 94 PRODUCT_ID PRODUCT_NAME CATEGORY 95 found when 96 1502 macle22 macle2_cate 97 1503 macle3 macle2_cate 98 1504 macle macle1_cate 99 1505 macle5 limit 101 macle1_cate meets delete where, but does not meet conditions in on, so it is not deleted .!!!!!!

When writing SQL statements, we often encounter a large number of Insert/Update statements at the same time, that is, when there is a record, Update (Update), when there is no data, insert ).

Merge format:

1 MERGE INTO table_name alias1 2 USING (table|view|sub_query) alias23 ON (join condition) 4 WHEN MATCHED THEN 5     UPDATE table_name 6     SET col1 = col_val1, 7         col2     = col2_val 8 WHEN NOT MATCHED THEN 9     INSERT (column_list) VALUES (column_values);

To explain the Merge syntax in Chinese, it is:

Select data in alias2,Each entryBoth are compared with alias1 for ON (join condition). If they match, Update is performed. If they do not match, Insert is performed ).