Summary of merge into usage in Oracle

The merge statement is a new syntax for merging the UPDATE and INSERT statements oracle9i.
With the merge statement, another table is queried based on the join criteria of one table or subquery,
The connection condition matches the execution insert on the update that cannot be matched.
This syntax only needs a full table scan to complete the whole work, the execution efficiency is higher than insert+update.

Grammar:

MERGE into [Your table-name] [rename your table here]

using ([Write your query here]) [rename your query-sql and using just like a table]

On ([conditional expression here] and [...] ...)

When mathed then [here you can execute some update SQL or something else]

When isn't mathed then [execute something else here!]

The merge in Oracle 10g has some of the following improvements:
1. Update or INSERT clauses are optional
2. Update and INSERT clauses can be added to the WHERE clause
3. The on condition uses the constant filter predicate to insert all rows into the target table without the need to connect the source and target tables
4, the UPDATE clause can be followed by a delete clause to remove some unwanted

Here's my own understanding:
The 1th meaning is that you can write only when matched then or only when the matched then is in the merge into syntax. Don't repeat it here.
The 2nd meaning is that we can add a where condition after the then update or INSERT statement, such as the need to insert an ID in table A when it does not exist in table B. When present, the name of a table is not empty when it is updated. In this case, the condition needs to be +where after the update sentence.
The 3rd meaning is that we can not associate the target table with the using table in the on condition, and you can write conditions such as 1=1 in the on condition. For example, we have this requirement: when the Isalladd parameter is false, a normal merge into is performed. When the Isalladd parameter is true, all insertions are performed. We can use this new feature to filter the conditions in on in order to achieve a SQL to meet the business purpose.
The 4th meaning is that we can not only insert updates in merge into, but also delete the matching data. This point is very important, personally think that the 4th is the improvement of the most we need to learn a little. For example, we have this demand: a table in the period of secondary school students ID, test year, test results, final exam results four fields. We do a bulk delete function for final exam results and delete this data when there is no midterm result. When there is a midterm, the final exam results are empty. Traditional notation will require the deletion of the information in the list of people whose database is queried to see if each of these is performed in a modified or deleted way, respectively. All we need now is
MERGE into A
USING (TABLE EMPLOYEE) B
On (A.employeeid = B.employeeid and a.year = b.year)
When matched then
UPDATE SET Midterm results = ""
DELETE WHERE Final Exam result is NULL

Special Note:
Delete words can only be written in the matched case, the mismatch can not be deleted when the error.
When delete follows the update, the DELETE clause is filtered for the modified data of the update sentence. For example, to delete all of the c field = "1" of data, the update sentence will all the data of the C field is updated to 1, then all data will be deleted, instead of the original 1 of the data.

Summary of merge into usage in Oracle