The PL/SQL MERGE statement in Oracle database is a powerful and flexible way to perform conditional insert, update, or delete operations in a single SQL statement. It is also commonly referred to as an “upsert” operation, as it can insert a new row or update an existing one based on a specified condition.
The MERGE statement is used to merge two or more rows into one row. This is useful when you want to update or insert data into a table, and the data already exists in another table.
The syntax for 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, ...);
Let’s break down the components of the MERGE statement:
target_table: This is the table that you want to modify.
source_table: This is the table or subquery that provides the data to be merged into the target table.
ON (condition): This specifies the condition for matching rows between the target and source tables. When this condition is met, the statement performs an UPDATE; otherwise, it performs an INSERT.
WHEN MATCHED THEN UPDATE: This clause defines the action to be taken when a matching row is found. You specify the columns to be updated and their new values.
WHEN NOT MATCHED THEN INSERT: This clause defines the action to be taken when no matching row is found. It specifies the columns to be inserted and their values.
MERGE INTO customers c1 USING new_customers c2 ON (c1.customer_id = c2.customer_id) WHEN MATCHED THEN UPDATE SET name = c2.name, city = c2.city WHEN NOT MATCHED THEN INSERT (name, city) VALUES (c2.name, c2.city) ;
The PL/SQL Merge statement is a very powerful tool that can be used to make changes to a database table. When used correctly, it can save you a lot of time and effort. For example, you can use the MERGE statement to update customer information in one table with customer information from another table.