PL/SQL Merge

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 streamlines the process of handling data changes in a more efficient and concise manner compared to using separate INSERT, UPDATE, and DELETE statements.

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:

Syntax:

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.

Example:

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.