The MERGE Statements

In Oracle, the MERGE statement is a powerful and flexible way to perform an "upsert" operation, which combines the functionality of both INSERT and UPDATE statements. The MERGE statement allows you to conditionally insert a row into a table or update it if it already exists, based on a specified condition. The basic syntax of the MERGE statement is as follows:

MERGE INTO target_table 
USING source_table 
ON (conditional expression) -- MUST be enclosed by parentheses!
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...)
;

Let's break down the components of the MERGE statement:

• target_table: The table that you want to modify (insert or update)
• source_table: The table or subquery providing the data that you want to merge into the target table.
• ON (conditional expression): The condition that determines whether a row in the source matches a row in the target. If the condition is true, the rows are considered "matched."
• WHEN MATCHED THEN UPDATE: Specifies the action to be taken when a match is found. You provide the columns to be updated and their new values.
• WHEN NOT MATCHED THEN INSERT: Specifies the action to be taken when no match is found. You provide the columns to be inserted and their values.

To illustrate, let's consider the following two tables:

In the my_pokemon table above, there are two rows whose pokemon_id is 252 and 254, respectively. Query in the line 2 retrieved 10 rows with id ranging from 251 to 260. Suppose that you want to update type1 and type2 in the my_pokemon table if there is any matching pokemon_id is found from the query. You may also update pokemon_level to be 5.

Among the Pokémons retrieved from the query in the line 2, there are 8 rows whose id value is not matching with any rows in the pokemon table. Let's say that you also want to insert those rows into my_pokemon table. You can perform those tasks with a MERGE statement as follows:

In this example, the my_pokemon is the target table and pokemon is the source. They are aliased by t and s, respectively. The MERGE statement compares rows based on the Pokémon ID. If a match is found, update the target table, if no match is found, it inserts a new row with the pokemon_id, caught_at, type1, and type2 from the source table.

There isn't anything you can do with MERGE that you cannot already do with some combination of those three DML statements. However, if it's possible to use MERGE as an alternative to executing two or more DML statements, then MERGE is preferable since it combines multiple DML actions into a single SQL statement, resulting in a single pass through the database. The MERGE statement is particularly useful when dealing with data synchronization or loading scenarios, where you need to ensure that your target table is kept in sync with the source data.