MERGE IN SQL

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:

Target Table (employees): The table where existing data is stored.
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 in Oracle:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER(10, 2)
);

Insert initial 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 in Oracle:

CREATE TABLE new_employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER(10, 2)
);

-- Insert new 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

In Oracle, the MERGE syntax is very similar to what was shown previously. We use the USING clause to join the new_employees table to the employees table, and then define the operations for matching and non-matching records.

MERGE INTO employees e
USING new_employees 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:

1. Employee ID 2 (Jane Smith): Exists in both tables, but the salary in new_employees is higher (65000 vs. 60000), so the MERGE updates her salary in the employees table.

2. Employee ID 3 (Mark Johnson): Exists in both tables, but the salary remains unchanged (70000). No updates are made because the data is the same.

3. Employee ID 4 (Emily Davis): Does not exist in the employees table, so the MERGE inserts a new row for her.

Step 3: Query the Final Data in the employees Table

After executing the MERGE, the employees table will have the following data:

SELECT * FROM employees;

Explanation in Oracle SQL Context:

1) John Doe (ID 1): Unaffected because he wasn’t in the new_employees table.

2) Jane Smith (ID 2): Her salary is updated from 60000 to 65000, reflecting the change in the new_employees table.

3) Mark Johnson (ID 3): Remains unchanged since there was no difference in the salary between both tables.

4) Emily Davis (ID 4): Is added as a new employee because she was not present in the employees table before.

Summary in Oracle:

The MERGE statement allows efficient upsert operations (update existing records or insert new ones).

It reduces the need to write separate UPDATE and INSERT statements by combining both operations into a single SQL statement.

It uses a combination of MATCHED and NOT MATCHED conditions to control when to update or insert data, improving performance for data synchronization scenarios.