In the context of Oracle SQL, the MERGE statement works similarly but with Oracle's specific syntax and behavior. Let's walk through the same example adapted for Oracle SQL.
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:
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.
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.
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:
John Doe (ID 1): Unaffected because he wasn’t in the new_employees table.
Jane Smith (ID 2): Her salary is updated from 60000 to 65000, reflecting the change in the new_employees table.
Mark Johnson (ID 3): Remains unchanged since there was no difference in the salary between both tables.
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.
Top comments (1)
The MERGE statement in SQL allows you to perform an INSERT, UPDATE, or DELETE operation based on whether a condition matches existing records in a target table. This is useful for synchronizing tables, especially in data warehousing.