The MERGE statement in SQL is a powerful tool that allows you to perform multiple operations (INSERT, UPDATE, DELETE) on a target table based on the data from a source table. It combines the functionality of the INSERT, UPDATE, and DELETE statements into a single statement, making it efficient and convenient for managing data synchronization between tables.
The basic syntax of the MERGE statement is as follows:
MERGE INTO target_table AS T
USING source_table AS S
ON T.column_name = S.column_name
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)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Let's break down the components of the MERGE statement:
- target_table: This is the table where the data will be merged or updated.
- source_table: This is the table that provides the data for the merge operation.
- ON: This specifies the join condition between the target and source tables.
- WHEN MATCHED: This section defines what to do when a match is found between the target and source tables.
- UPDATE SET: This specifies the columns in the target table that will be updated with the corresponding values from the source table.
- WHEN NOT MATCHED: This section defines what to do when no match is found between the target and source tables.
- INSERT: This specifies the columns in the target table where new rows will be inserted, using the values from the source table.
- WHEN NOT MATCHED BY SOURCE: This section defines what to do when there are rows in the target table that do not have a corresponding match in the source table.
- DELETE: This deletes the rows from the target table that do not have a match in the source table.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);
CREATE TABLE employee_updates (
id INT PRIMARY KEY,
salary DECIMAL(10, 2)
);
We want to update the salary of employees based on the data in the employee_updates table. Here's how we can achieve this using the MERGE statement:
MERGE INTO employees AS E
USING employee_updates AS U
ON E.id = U.id
WHEN MATCHED THEN
UPDATE SET E.salary = U.salary;
In this example, the MERGE statement matches the id column in both tables and updates the salary column in the employees table with the corresponding values from the employee_updates table.
The MERGE statement is a powerful tool for managing data synchronization and can save you from writing multiple separate statements for INSERT, UPDATE, and DELETE operations. It provides a concise and efficient way to handle complex data manipulation tasks.
Remember to always test your MERGE statements thoroughly and ensure that you have proper backup mechanisms in place before performing any data modifications.
I hope this detailed explanation and example help you understand the MERGE statement in SQL. If you have any query , feel free to ask!