In the form of merge into:

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

Function: To judge whether table B and table A meet the conditions of ON, if they are satisfied, update table A with table B, if not, insert table B data into table A, but there are many options as follows:
1. Normal mode

2. Only update or insert

3. Conditional update or conditional insert

4. Full insert implementation

5. update with delete (think it can be implemented in 3)
Let's test them one by one.

The test builds the following table:

1. create table A_MERGE  
2. (  
3.   id   NUMBER not null,  
4.   name VARCHAR2(12) not null,  
5.   year NUMBER  
6. );  
7. create table B_MERGE  
8. (  
9.   id   NUMBER not null,  
10.   aid  NUMBER not null,  
11.   name VARCHAR2(12) not null,  
12.   year NUMBER,  
13.   city VARCHAR2(12)  
14. );  
15. create table C_MERGE  
16. (  
17.   id   NUMBER not null,  
18.   name VARCHAR2(12) not null,  
19.   city VARCHAR2(12) not null  
20. );  
21. commit;  

A screenshot of the surface structure is shown in the following figure:

A_MERGE table structure:

B_MERGE Table Structure

C_MERGE Table Structure

1. Normal mode

First insert test data into A_MERGE and B_MERGE:

1. insert into A_MERGE values(1,'liuwei',20);  
2. insert into A_MERGE values(2,'zhangbin',21);  
3. insert into A_MERGE values(3,'fuguo',20);  
4. commit;  
5.   
6. insert into B_MERGE values(1,2,'zhangbin',30,'Jilin');  
7. insert into B_MERGE values(2,4,'yihe',33,'Heilongjiang');  
8. insert into B_MERGE values(3,3,'fuguo',,'Shandong');  
9. commit;  

At this time, the data screenshots in the A_MERGE and B_MERGE tables are as follows:

A_MERGE table data:

B_MERGE table data:

Then merge into is used to update the data in A_MERGE with B_MERGE:

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

At this point, the table data in A_MERGE is screenshot as follows:

2. update mode only
First, insert two data into B_MERGE to show that only update has no insert, and one data must exist in A.

When another data does not exist in A, insert data statements as follows:

1. insert into B_MERGE values(4,1,'liuwei',80,'Jiangxi');  
2. insert into B_MERGE values(5,5,'tiantian',23,'Henan');  
3. commit;  

At this time, the A_MERGE and B_MERGE table data screenshots are as follows:
A_MERGE table data screenshot:

Data screenshot of B_MERGE table:

Then B_MERGE is used to update A_MERGE again, but only update, not insert.

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

After merge, the A_MERGE table data screenshot is as follows: you can see that only the age of AID=1 has been updated, and no data of AID=4 has been inserted.

3. insert-only mode
First, change one of the data in B_MERGE, because the data added during the last test update was not inserted into A_MERGE, which can be used this time.

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

The tables of A_MERGE and B_MERGE at this time 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, but no update is written:

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

At this time, the table data of A_MERGE is screenshot as follows:

4. insert and update with where condition.
After conditional matching is done in on, we can make conditional judgment again in the later insert and update to control which records to update and which to insert.
The sql code of the test data is as follows. We changed two names and added two personnel information in B_MERGE, but they came from different provinces.
So we can control which can be modified and which can be inserted by adding provincial conditions:

1. update B_MERGE set name='yihe++' where id=2;  
2. update B_MERGE set name='liuwei++' where id=4;  
3. insert into B_MERGE values(6,6,'ningqin',23,'Jiangxi');  
4. insert into B_MERGE values(7,7,'bing',24,'Ji'an');  
5. commit;  

A_MGERGE table data screenshots are as follows:

B_MERGE table data:

Then B_MERGE is used to update A_MERGE, but conditions are added after insert and update respectively to control the update and insertion of data:

1. merge into A_MERGE A USING (select B.AID,B.name,B.year,B.city from B_MERGE B) C   
2. ON(A.id=C.AID)   
3. when matched then  
4.   update SET A.name=C.name where C.city != 'Jiangxi'  
5. when not matched then  
6.   insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city='Jiangxi';  
7. commit;  

At this point, the A_MERGE screenshot is as follows:

5. Unconditional insert.
Sometimes we need to insert all the data in one table into another table, at which point we can add constant filtering predicates to achieve, so that it only satisfies.
Matching and mismatching, so that only update or insert. Where we want unconditional full insertion, we just need to set the condition in on to permanent vacation.
Yes. Update the C_MERGE code with B_MERGE as follows:

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

Data screenshots of the C_MERGE table before merge are as follows:

B_MERGE data screenshots are as follows:

Data screenshots of the C_MERGE table after merge are as follows:

6. update with delete
MERGE provides the option to clear rows while 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 ON condition will not be deleted from the table.

But I think this update with where condition is almost the same as that with control update, which can be completely implemented with where condition update.

Reprinted from merge into