MERGE in SQL

ChatGPT

A conversational AI system that listens, learns, and challenges

chatgpt.com

The MERGE statement in SQL is used to combine INSERT, UPDATE, and DELETE operations into a single statement. It is commonly used for upsert (update + insert) scenarios, where you need to update records if they exist or insert new records if they do not.

The syntax typically follows this structure:

MERGE INTO target_table AS target
USING source_table AS source
ON target.matching_column = source.matching_column
WHEN MATCHED THEN
UPDATE SET target.column1 = source.column1, target.column2 = source.column2
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES (source.column1, source.column2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE; -- optional

Let's go through an example using MERGE in SQL, with sample data. Imagine we have two tables:

1. Target Table (employees): The table where existing data is stored.

2. Source Table (new_employees): The table containing new data that we want to merge into the target.

Step 1: Create Tables and Insert Sample Data

Create employees Table:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);

-- Insert some sample data into the employees table
INSERT INTO employees (employee_id, name, salary)
VALUES
(1, 'John Doe', 50000),
(2, 'Jane Smith', 60000),
(3, 'Mark Johnson', 70000);

Create new_employees Table:

CREATE TABLE new_employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);

-- Insert sample data into the new_employees table
INSERT INTO new_employees (employee_id, name, salary)
VALUES
(2, 'Jane Smith', 65000), -- Update: salary change for existing employee
(3, 'Mark Johnson', 70000), -- No change for existing employee
(4, 'Emily Davis', 75000); -- Insert: new employee

Step 2: Use MERGE to Update or Insert Data

Now, we will merge the data from new_employees into employees.

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

What happens:

Employee ID 2 (Jane Smith) exists in both tables, but her salary in new_employees is higher (65000 vs. 60000), so her salary is updated.

Employee ID 3 (Mark Johnson) exists in both tables with the same salary, so no changes are made.

Employee ID 4 (Emily Davis) does not exist in the employees table, so a new row is inserted.

Final Data in employees Table After MERGE:

SELECT * FROM employees;

Explanation:

Row for John Doe remains unchanged since he wasn’t present in new_employees.

Jane Smith had her salary updated from 60000 to 65000.

Mark Johnson remains unchanged as there were no changes in his data.

Emily Davis was added as a new employee.

This is how MERGE simplifies the combination of UPDATE and INSERT in one operation.

The MERGE statement in SQL combines INSERT, UPDATE, and optionally DELETE operations. It updates records if they exist and inserts new records if they don't.

Example:

1. Target Table: employees

2. Source Table: new_employees

Using MERGE, existing employees (like Jane) get updated, and new employees (like Emily) are inserted.

Final Output:

The employees table reflects updated data from new_employees, with new rows inserted and existing ones updated where necessary.