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.