Oracle-merge usage

Oracle9i introduces the MERGE command. You can execute the inserts and updates operations on a table in an SQL statement at the same time. the MERGE command selects rows from one or more data sources to update or inserting to one or more tables. in Oracle 10g, MERGE has the following improvements:

 

 

You can add the WHERE clause to the UPDATE or INSERT clause to skip the update or insert operation. the following example updates the table's PRODUCTS data based on the table NEWPRODUCTS, but the field CATEGORY must be matched at the same time:

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p. product_id = np. product_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p. product_name = np. product_name

7 WHERE p. category = np. category;

 

2 rows merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1502 olympus camera sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

SQL>

SQL> rollback; DE>

 

 

 

In this example, the product IDs are 1666, 1666, and match the ON condition, but the category of does not match. therefore, the MERGE command only updates two rows of data. the following example shows how to use the WHERE clause in both Updates and Inserts clauses:

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p. product_id = np. product_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p. product_name = np. product_name,

7 p. category = np. category

8 WHERE p. category = 'dve'

9 WHEN NOT MATCHED THEN

10 INSERT

11 VALUES (np. product_id, np. product_name, np. category)

12 WHERE np. category! = 'Books'

SQL>/

 

1 row merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1502 OLYMPUS IS50 sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

 

SQL> DE>

 

 

Note that because the WHERE clause INSERT does not INSERT all rows that do not match the ON condition to the table PRODUCTS.

3. Unconditional Inserts

 

You can insert data from the source table to the target table without connecting the source table to the target table. this is useful when you want to insert all rows into the target table. oracle 10 Gb now supports constant filter predicates in the ON condition. for example, ON (1 = 0 ). the following example inserts rows from the source table to the table PRODUCTS without checking whether these rows exist in the table PRODUCTS:

 

 

 

 

 

 

 

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (1 = 0)

4 WHEN NOT MATCHED THEN

5 INSERT

6 VALUES (np. product_id, np. product_name, np. category)

7 WHERE np. category = 'books'

SQL>/

 

1 row merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1502 OLYMPUS IS50 sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

1700 WAIT INTERFACE BOOKS

6 rows selected.

SQL> DE>

 

 

 

4. Newly Added DELETE clause

 

MERGE in Oracle 10 Gb provides the option to clear rows when performing data operations. you can include the DELETE clause in the when matched then update clause. the DELETE clause must have a WHERE condition to DELETE rows that match certain conditions. rows that match the delete where condition but do not match the ON condition are not deleted from the table.

 

In the following example, the DELETE clause is verified. We merge data from the table NEWPRODUCTS to the table PRODUCTS, but DELETE the rows whose category is into NCS.

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p. product_id = np. product_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p. product_name = np. product_name,

7 p. category = np. category

8 delete where (p. category = 'your NCS ')

9 WHEN NOT MATCHED THEN

10 INSERT

11 VALUES (np. product_id, np. product_name, np. category)

SQL>/

 

4 rows merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

SQL> DE>

 

 

 

The row with the product ID 1502 is deleted from the table PRODUCTS because it matches both the ON condition and the delete where condition. the row with the product ID 1501 matches the delete where condition but does not match the ON condition, so it is not deleted. the row with the product ID 1700 does not match the ON condition, so the table PRODUCTS is inserted. rows with the product ID 1601 and 1666 match the ON condition but do not match the delete where condition, so they are updated as values in the NEWPRODUCTS table.

 

Example 2:

Application of Merge Into statement in Oracle instead of Insert/Update

Motivation:

You want to use an SQL statement in Oracle to directly perform Insert/Update operations.

Note:

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 ).

Practice:

Next we will have a task with a table T with two fields a and B. We want to Insert/Update in Table T. If so, we will Update the value of B in table T, if not, insert a record. In Microsoft's SQL syntax, you can simply make a judgment. The syntax in SQL Server is as follows:

If exists (select 1 from T where T. a = '000000') update T set T. B = 2 Where T. a = '000000' else insert into T (a, B) values ('000000', 2 );

The preceding statement indicates that if a = '20160301' record exists in table T, set the value of B to 2. Otherwise, Insert a record a = '20160301 ', B = 2 records to T.

However, there is a problem in Oracle. Remember that there is a Merge into statement after Oracle 9i that can be used for Insert and Update at the same time. The Merge syntax is as follows:

Merge into table_name alias1

USING (table | view | sub_query) alias2

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 );

The above syntax should be easy to understand, so we should write it again based on the above logic.

Merge into t T1

USING (SELECT a, B FROM T WHERE t. a = '000000') T2

ON (T1.a = T2.a)

WHEN MATCHED THEN

Update set T1. B = 2

WHEN NOT MATCHED THEN

INSERT (a, B) VALUES ('20170101', 2 );

The preceding statement seems to be correct, right? In fact, this statement can only be updated, but cannot be inserted. Where is the error?

In fact, in Oracle, the Merge statement was originally used to update the whole table, that is, the commonly used Syntax of ETL tools, with emphasis on Using.

To explain the Merge syntax in Chinese, it is:

For the data selected in alias2, each record is compared with alias1 ON (join condition). If it matches, the Update operation is performed (Update). If it does not match, insert ).

Therefore, strictly speaking, "in a Merge statement with both Insert and Update syntax, the total number of Insert/Update records is the number of alias2 records in the Using statement ."

The above statement explains why the statements written above can only be updated, but cannot be inserted. Because no data can be selected, how can we perform Insert :)

It is much easier to change to the correct statement, as shown below:

Merge into t T1

USING (SELECT '000000' AS a, 2 AS B FROM dual) T2

ON (T1.a = T2.a)

WHEN MATCHED THEN

Update set T1. B = T2. B

WHEN NOT MATCHED THEN

INSERT (a, B) VALUES (T2.a, T2. B );

Query Result, OK!

Note:

If you do not understand the principle of the Merge statement, the Merge statement is a dangerous statement, especially when you only want to update a record, you may have updated all the data in the entire table ..... khan !!!

One of the mistakes I have made is as follows. Do you see what the problem is?

Merge into t T1

USING (SELECT Count (*) cnt from t where t. a = '000000') T2

ON (T2.cnt> 0)

WHEN MATCHED THEN

Update set T1. B = T2. B

WHEN NOT MATCHED THEN

INSERT (a, B) VALUES (T2.a, T2. B );

 

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.

 

First, create an example table:

 

DE> create table PRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2 (60 ),

CATEGORY VARCHAR2 (60)

);

 

Insert into PRODUCTS values (1501, 'vivitar 123456', 'invalid NCS ');

Insert into PRODUCTS values (1502, 'olympus is50', 'using NCS ');

Insert into PRODUCTS values (1600, 'play gym', 'toys ');

Insert into PRODUCTS values (1601, 'lamaze', 'toys ');

Insert into PRODUCTS values (1666, 'Harry POTTER ', 'dve ');

Commit;

 

Create table NEWPRODUCTS

(

PRODUCT_ID INTEGER,

PRODUCT_NAME VARCHAR2 (60 ),

CATEGORY VARCHAR2 (60)

);

 

Insert into NEWPRODUCTS values (1502, 'olympus CAMERA ', 'using NCS ');

Insert into NEWPRODUCTS values (1601, 'lamaze', 'toys ');

Insert into NEWPRODUCTS values (1666, 'Harry POTTER ', 'toys ');

Insert into NEWPRODUCTS values (1700, 'Wait interface', 'books ');

Commit; DE>

1. The UPDATE or INSERT clause that can be omitted

 

In Oracle 9i, the MERGE statement requires you to specify both the INSERT and UPDATE clauses. in Oracle 10 Gb, You can omit one of the UPDATE or INSERT clauses. the following example shows how to update the PRODUCTS information of the table based on whether the PRODUCT_ID field of the table NEWPRODUCTS matches:

 

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p. product_id = np. product_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p. product_name = np. product_name,

7 p. category = np. category;

 

3 rows merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1502 olympus camera sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

SQL>

SQL> ROLLBACK;

Rollback complete.

SQL> DE>

 

 

 

In the preceding example, the MERGE statement affects the rows whose product IDs are 1502,160 1 and 1666. their product names and classes are updated to values in the newproducts table. in the following example, the UPDATE clause is omitted and the new PRODUCT_ID In the table NEWPRODUCTS is inserted into the table PRODUCTS. data that can match the PRODUCT_ID in the two tables is not processed. in this example, you can see that the row with PRODUCT_ID = 1700 is inserted into the table PRODUCTS.

 

DE> SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p. product_id = np. product_id)

4 WHEN NOT MATCHED THEN

5 INSERT

6 VALUES (np. product_id, np. product_name,

7 np. category );

 

1 row merged.

 

SQL> SELECT * FROM products;

 

PRODUCT_ID PRODUCT_NAME CATEGORY

----------------------------------------

1501 VIVITAR 35 MM sans NCS

1502 OLYMPUS IS50 sans NCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

1700 wait interface booksde>

2. Conditional Updates and Inserts clauses