MERGE

The MERGE statement in Oracle SQL is a powerful and versatile command that allows you to perform conditional INSERT, UPDATE, and DELETE operations on a target table based on data from a source table or subquery. It is often referred to as an UPSERT because it combines both INSERT and UPDATE operations into a single query.

 

1. Basic Syntax of the MERGE Statement

The basic syntax of the MERGE statement is:

MERGE INTO target_table t
USING source_table s
ON (t.match_column = s.match_column)
WHEN MATCHED THEN
    UPDATE SET t.column1 = s.column1, t.column2 = s.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (s.column1, s.column2, ...);
  • target_table: The table you want to modify (i.e., update or insert into).
  • source_table: The table or subquery providing the data to compare against the target table.
  • match_column: The column(s) used to compare records between the target and source tables.
  • WHEN MATCHED: Specifies the actions (like UPDATE) to take when there is a match between the target and source.
  • WHEN NOT MATCHED: Specifies the actions (like INSERT) to take when there is no match.

 

2. How MERGE Works

The MERGE statement compares each row in the source table (or subquery) with the corresponding row in the target table based on the ON condition. There are three possible outcomes:

  1. When a match is found (the condition in ON is true):
    • The UPDATE clause is executed to modify the existing record in the target table.
  2. When no match is found (the condition in ON is false):
    • The INSERT clause is executed to insert a new record into the target table.

 

3. Example of Using MERGE for an UPSERT Operation

Example 1: Using MERGE to Update Existing Records and Insert New Records

Suppose we have two tables, employees and new_employees. We want to update the employees table with the new data from new_employees, and if any records do not exist, insert them.

MERGE INTO employees e
USING new_employees ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = ne.salary, e.department_id = ne.department_id
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary, department_id)
    VALUES (ne.employee_id, ne.employee_name, ne.salary, ne.department_id);
  • Matching Rows: If an employee_id exists in both the employees and new_employees tables, the UPDATE operation is triggered to update the salary and department_id in the employees table.
  • Non-Matching Rows: If an employee_id does not exist in the employees table, the INSERT operation is triggered to add the new record.

 

4. Handling Multiple Actions in MERGE

The MERGE statement can handle more than just INSERT and UPDATE. It can also handle conditional DELETE operations in certain cases.

Example 2: Deleting Records Based on a Condition

Suppose you want to delete records from the employees table if they are not present in the new_employees table.

MERGE INTO employees e
USING new_employees ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = ne.salary, e.department_id = ne.department_id
WHEN NOT MATCHED THEN
    DELETE;

In this example:

  • When there is a match, an UPDATE happens.
  • When there is no match (the employee is no longer in the new_employees table), the record is deleted from the employees table.

 

5. Using Subqueries in the MERGE Statement

You can use a subquery as the source in a MERGE statement, rather than a static table. This is useful when you want to merge based on more complex criteria.

Example 3: Using a Subquery as the Source

MERGE INTO employees e
USING (SELECT employee_id, salary, department_id
       FROM new_employees
       WHERE hire_date > '2024-01-01') ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = ne.salary, e.department_id = ne.department_id
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary, department_id)
    VALUES (ne.employee_id, ne.employee_name, ne.salary, ne.department_id);

Here, the source is a subquery that filters the new_employees table to only include those hired after January 1st, 2024.

 

6. Conditional Logic with the MERGE Statement

You can also add more complex conditional logic within the UPDATE or INSERT actions.

Example 4: Using Conditional Logic in MERGE

MERGE INTO employees e
USING new_employees ne
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = 
        CASE 
            WHEN ne.salary > e.salary THEN ne.salary
            ELSE e.salary
        END
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary, department_id)
    VALUES (ne.employee_id, ne.employee_name, ne.salary, ne.department_id);

In this example:

  • The UPDATE logic only updates the salary if the new salary is greater than the current salary.
  • If no match is found, the new employee record is inserted.

 

7. Using MERGE with Multiple Conditions

You can also use multiple conditions to determine when a row is matched or not.

Example 5: MERGE with Multiple Conditions

MERGE INTO employees e
USING new_employees ne
ON (e.employee_id = ne.employee_id AND e.department_id = ne.department_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = ne.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary, department_id)
    VALUES (ne.employee_id, ne.employee_name, ne.salary, ne.department_id);

In this example:

  • The ON clause checks both employee_id and department_id for a match.
  • If a match is found, the salary is updated.
  • If no match is found, the record is inserted.

 

8. Performance Considerations for MERGE

While the MERGE statement is convenient, there are some performance considerations to keep in mind:

  • Indexes: Ensure that there are indexes on the columns used in the ON condition (usually the primary key or unique key columns). This will improve the performance of the comparison between the target and source tables.
  • Large Data Sets: When merging large data sets, consider breaking the operation into smaller chunks if possible to avoid locking issues.
  • Transaction Control: The MERGE statement is an atomic operation, meaning that if an error occurs, none of the changes are committed to the database. It’s important to manage transactions properly when using MERGE in scripts or applications.

 

9. Limitations of the MERGE Statement

  • No Multiple Updates: You can only perform one update per row in a MERGE. If you need to update a row multiple times based on different conditions, you may need to use multiple MERGE statements or other approaches.
  • Subqueries and Aggregates: While MERGE allows subqueries as a data source, they can be limited by the complexity of the query and the performance of the subquery.

 

No comments:

Post a Comment