Merge updates or inserts the same table. merge updates the table.

Merge updates or inserts the same table. merge updates the table.
For a business logic, you must first determine whether a record exists in the Database. If yes, update the record. If no record exists, insert the record.
The practice before the application is:
1. Use conditions to determine the number of records in the database.
2.1 If count (*)> 0, UPDATE is performed.
2.2 If count (*) = 0, the INSERT operation is executed.
Or
1. insert records first.
2.1 If a primary key error occurs for the ORA-001, a record exists and the UPDATE operation is performed.
2.2 If no error is reported, the insertion is considered complete.

I think the above two methods can implement this business logic. The difference is that the second method may only require one SQL operation, provided that most records do not exist. If most operations are UPDATE operations, this can be changed:
1. Update first.
2.1 if the number of updates is greater than 0, a record exists and execution is complete.
2.2 If the number of updates is 0, no record exists and the INSERT operation is executed.

The worst case of the above logic is to execute two SQL statements. If the data volume is small, the time consumed can be ignored. However, if the data volume is large, the time consumed may be doubled. In this case, you may consider using merge. Generally, merge is used to import data from one table to another, but it can operate on the same table. For example:
Requirement: RULE_COLLISION table: update the collision_count Field Based on app_name, rule_id, and start_time, or insert a new record directly.
SQL:
Merge into RULE_COLLISION t1
Using (SELECT 'test' app_name, 'timelimit _ COMPONENT 'MODULE, 'Rule 1 'rule__id, 3 COLLISION_COUNT, to_date ('2017-07-21', 'yyyy-mm-dd ') start_time from dual) t2
On (t1.app _ name = t2.app _ name AND t1.rule _ id = t2.rule _ id AND t1.start _ time = t2.start _ time)
When matched then
Update SET t1.collision _ count = t2.collision _ count
When not matched then
Insert values (t2.app _ name, t2.MODULE, t2.RULE _ ID, t2.COLLISION _ COUNT, t2.start _ time );

Using the pseudo table dual to implement self-update or insert of the RULE_COLLISION table is the same as the above logic, but only one SQL statement is executed as follows:

Execution Plan
----------------------------------------------------------
Plan hash value: 3989089639
Bytes -----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------------------
| 0 | merge statement | 1 | 322 | 2 (0) | 00:00:01 |
| 1 | MERGE | RI_RULE_COLLISION_DETAIL |
| 2 | VIEW |
| 3 | nested loops outer | 1 | 224 | 2 (0) | 00:00:01 |
| 4 | fast dual | 1 | 2 (0) | 00:00:01 |
| 5 | table access by index rowid | RI_RULE_COLLISION_DETAIL | 1 | 224 | 0 (0) | 00:00:01 |
| * 6 | index unique scan | RULE_COLLISION_ID | 1 | 0 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
6-access ("T1 ". "APP_NAME" (+) = 'galt' AND "T1 ". "RULE_ID" (+) = 'Rule 1' AND "T1 ". "START_TIME" (+) = TO_DATE ('2014-07-21 00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))

Note
-----
-Dynamic sampling used for this statement (level = 2)

Statistics
----------------------------------------------------------
0 recursive cballs
3 db block gets
2 consistent gets
0 physical reads
0 redo size
829 bytes sent via SQL * Net to client
1315 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Here we use nested loops outer instead of hash join. Is the two table operation different? You need to study this experiment later. If you have any experts, please consult!
SQL 2008 if the same table uses merge to update data that does not exist when data exists, insert the data code in the table as follows:

Your own merge
It seems impossible to execute the "when not matched then" branch.

When not matched then
This operation is performed when the source table has records but the target table has no records.

How can we achieve that the source table has records, but the target table has no records?
 
How to insert data from one table to another in oracle databases? Both tables exist.

If the source table has data, the target table has no data.
Insert into target table SELECT * FROM source table.

If data exists in the target table of the source table, update the target table if data exists. If no data exists, insert it.
Merge into target table
USING source table
ON)
When matched then update set target table. Field = source table. Field -- update when matching
When not matched then insert values (source table. Field List) -- source table has, target table does NOT, INSERT
Reference: hi.baidu.com/...6.html