Use of mergeinto in Oracle

This command uses a statement to UPDATE and INSERT data to a table from one or more data sources. In Oracle9i, you must specify both UPDATE and INSERT

This command uses a statement to UPDATE and INSERT data to a table from one or more data sources. In Oracle 9i, you must specify both UPDATE and INSERT

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.
1. insert and update are optional. UPDATE and INSERT can be followed by the WHERE clause 3. In the ON condition, constants can be used to insert all rows to the target table, you do not need to connect to source table and target table 4. The UPDATE clause can be followed by delete to remove unnecessary rows.
Example:
Create table PRODUCTS
(
PRODUCT_IDINTEGER,
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_IDINTEGER,
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;
1. omitted update or insert
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;
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.
2. When the conditions are not met, INSERT the data in the newproducts table into the products table.
Merge into products p
USING newproducts np
ON (p. product_id = np. product_id)
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name,
Np. category );
3. Conditional insert and update
Both insert and update have the where clause.
Merge into products p
USING newproducts np
ON (p. product_id = np. product_id)
WHEN MATCHED THEN
UPDATE
SET p. product_name = np. product_name,
P. category = np. category
WHERE p. category = 'dve'
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name, np. category)
WHERE np. category! = 'Books'
4. Unconditional insert
Merge into products p
USING newproducts np
ON (1 = 0)
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name, np. category)
WHERE np. category = 'books'
5. delete clause
1 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 delete where category = 'macle1 _ cate ';
PRODUCT_ID PRODUCT_NAME CATEGORY
-------------------------------------------------------------------------------
1502 macle22 macle2_cate
1503 macle3 macle2_cate
1504 macle maclesponcate
1505 macle5 macle5_cate
The maclepoliccate in 1504 meets the delete where condition, but does not meet the on condition, so it is not deleted .!!!!!! Key Points
-----------------------------------------------
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 );