How is the MERGE statement used in Oracle?

2 years ago
Noah Thompson
2 minutes

In Oracle database, the MERGE statement is used to perform both INSERT and UPDATE operations simultaneously. It checks specified conditions to determine if data exists in the target table, and then performs an UPDATE operation if it does, or an INSERT operation if it does not.

The basic syntax of the MERGE statement is as follows:

MERGE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2,...
WHEN NOT MATCHED THEN
    INSERT (column1, column2,...) VALUES (value1, value2,...)

The target_table is the table to be operated on, the source_table is the source table, and the condition is used to determine whether the data in the target_table and source_table match. When the data in the source table and target table match, the UPDATE operation is performed, and you can specify the columns that need to be updated and their corresponding values in the UPDATE clause. When the data in the source table and target table do not match, the INSERT operation is performed, and you can specify the columns that need to be inserted and their corresponding values in the INSERT clause.

The MERGE statement allows for easy batch insertion and updating of data, avoiding the need to repeatedly execute separate INSERT and UPDATE statements.

bannerAds