Enhanced Merge in Oracle 10 GB

Create test table
Create table test1
SELECT *
FROM all_objects
WHERE 1 = 2;
MATCHED and not matched clauses are optional

① Both clauses appear.
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN MATCHED THEN
Update set a. status = B. status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (B. object_id, B. status );
② ONLY insert operations, I .e. only the NOT MATCHED clause
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (B. object_id, B. status );
③ Only update operations, I .e. only MATCHED clauses
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN MATCHED THEN
Update set a. status = B. status;

The source table can contain the WHERE clause.
① Updates and inserts contain the WHERE clause.
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN MATCHED THEN
Update set a. status = B. status
WHERE B. status! = 'Valid'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (B. object_id, B. status)
WHERE B. status! = 'Valid ';
② Insert operations include the WHERE clause
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (B. object_id, B. status)
WHERE B. status! = 'Valid ';
③ Update operations include the WHERE clause
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN MATCHED THEN
Update set a. status = B. status
WHERE B. status! = 'Valid ';
DELETE clause

You can use this clause to DELETE records that meet both the ON Clause conditions and the delete where clause conditions in the source table.
Merge into test1
USING all_objects B
ON (a. object_id = B. object_id)
WHEN MATCHED THEN
Update set a. status = B. status
WHERE B. status! = 'Valid'
Delete where (B. status = 'valid ');