Usage and instance resolution of merge into in oracle, oraclemerge

Usage and instance resolution of merge into in oracle, oraclemerge

Merge into format:

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...) WHEN MATCHED THEN   [UPDATE sql] WHEN NOT MATCHED THEN   [INSERT sql] 

Purpose: Determine whether table B and table A meet the ON conditions. If yes, use table B to update Table A. If not, insert table B into Table A, but there are many options:

1. Normal Mode

2. Only update or insert

3. Conditional update or conditional insert

4. Full insert implementation

5. update with delete (I think it can be implemented using 3)

The following is a one-to-one test.

Create the following table for test:

create table A_MERGE (  id  NUMBER not null,  name VARCHAR2(12) not null,  year NUMBER ); create table B_MERGE (  id  NUMBER not null,  aid NUMBER not null,  name VARCHAR2(12) not null,  year NUMBER,  city VARCHAR2(12) ); create table C_MERGE (  id  NUMBER not null,  name VARCHAR2(12) not null,  city VARCHAR2(12) not null ); commit; 

Shows the table structure:

A_MERGE table structure:

B _MERGE table structure

C_MERGE table structure

1. Normal Mode

Insert test data to A_MERGE and B _MERGE first:

Insert into A_MERGE values (1, 'liuwei', 20); insert into A_MERGE values (2, 'hangzhou', 21); insert into A_MERGE values (3, 'fuguo ', 20); commit; insert into B _MERGE values (1, 2, 'hangbin', 30, 'jile'); insert into B _MERGE values (2, 4, 'yihe ', 33, 'heilongjiang '); insert into B _MERGE values (3, 3, 'fuguo', 'shandong'); commit;

The data in the_MERGE and B _MERGE tables is as follows:

A_MERGE table data:

B _MERGE table data:

Then use merge into to use B _MERGE to update the data in A_MERGE:

MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID) WHEN MATCHED THEN  UPDATE SET A.YEAR=C.YEAR  WHEN NOT MATCHED THEN  INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); commit; 

The table data in A_MERGE is as follows:

2. Only update mode

First, insert two pieces of data into B _MERGE to show that only update does not insert. One of the data must already exist in.

If the other data does not exist in A, the insert data statement is as follows:

Insert into B _MERGE values (, 'liuwei', 80, 'jiangxi '); insert into B _MERGE values (, 'tiantian', 23, 'henan '); commit;

The data in the_MERGE and B _MERGE tables is as follows:

A_MERGE table data:

B _MERGE table data:

Then, we use B _MERGE to update A_MERGE again, but only update, not insert.

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) WHEN MATCHED THEN  UPDATE SET A.YEAR=C.YEAR;   commit; 

After merge is complete, the data in the_MERGE table is as follows: we can find that only the age of AID = 1 is updated and no data with AID = 4 is inserted.

3. insert-only mode

First, change one data in B _MERGE, because the newly added data was not inserted into A_MERGE during the last test update. This time, it can be used.

update B_MERGE set year=70 where AID=2; commit; 

The table data of A_MERGE and B _MERGE are as follows:

A_MERGE table data:

B _MERGE table data:

Then, B _MERGE is used to update the data in A_MERGE. At this time, only insert is written, and update is not written:

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) WHEN NOT MATCHED THEN   insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); commit; 

The table data of A_MERGE is as follows:

4. insert and update with where conditions.

After condition matching is performed in the on statement, we can perform condition judgment on the records filtered by on in the insert and update statements to control the update and insert operations.

The SQL code for the test data is as follows. We modified two people's names in B _MERGE and added two people's information, but they came from different provinces,

Therefore, we can add province conditions to control which can be modified and which can be inserted:

Update B _MERGE set name = 'yihe ++ 'where id = 2; update B _MERGE set name = 'liuwei ++ 'where id = 4; insert into B _MERGE values (6, 6, 'ningqin', 23, 'jiangxi '); insert into B _MERGE values (, 'bin', 24, 'gianc'); commit;

The data in the_MGERGE table is as follows:

B _MERGE table data:

Then, B _MERGE is used to update A_MERGE. However, condition restrictions are added after insert and update to control data update and insertion:

Merge into A_MERGE a using (select B. AID, B. name, B. year, B. city from B _MERGE B) C ON (. id = C. AID) when matched then update set. name = C. name where C. city! = 'Jiangxi 'when not matched then insert (. ID,. name,. year) values (c. AID, C. name, C. year) where C. city = 'jiangxi '; commit;

At this time, A_MERGE is as follows:

5. Unconditional insert.

Sometimes we need to insert all the data in a table to another table. In this case, we can add a constant filter predicate so that it only matches and does not match, in this case, only update or insert is required. Here we want to insert all the conditions unconditionally, you just need to set the condition on to permanent false. Use B _MERGE to update the C_MERGE Code as follows:

merge into C_MERGE C USING (select B.AID,B.NAME,B.City from B_MERGE B) C ON (1=0) when not matched then  insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City); commit; 

The data in the C_MERGE table before merge is as follows:

B _MERGE data is as follows:

The data in the C_MERGE table after merge is as follows:

6. update with delete

MERGE 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 will not be deleted from the table.

However, I think the update with the where condition is similar, and it controls the update. It can be fully implemented using the update with the where condition.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.