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 (likeUPDATE
) to take when there is a match between the target and source.WHEN NOT MATCHED
: Specifies the actions (likeINSERT
) 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:
- 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. - 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 theemployees
andnew_employees
tables, theUPDATE
operation is triggered to update thesalary
anddepartment_id
in theemployees
table. - Non-Matching
Rows: If an
employee_id
does not exist in theemployees
table, theINSERT
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 theemployees
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 bothemployee_id
anddepartment_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 usingMERGE
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 multipleMERGE
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