Using MERGE Statement in Oracle Database 23ai

The MERGE statement in Oracle Database is a powerful command that combines the functionality of both INSERT and UPDATE. Instead of writing separate SQL commands, MERGE allows you to check whether a row exists and then decide whether to insert a new one or update the existing one. This is especially useful when synchronizing data between two tables. In Oracle Database 23ai, MERGE can also work with advanced features such as JSON and vector data, giving developers more flexibility for modern applications.

Understanding the MERGE Statement

The MERGE statement checks for a match between a source dataset and a target table. If a match is found, it updates the existing row; if no match is found, it inserts a new row.

The basic structure is:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
   UPDATE SET t.column1 = s.column1, t.column2 = s.column2
WHEN NOT MATCHED THEN
   INSERT (id, column1, column2)
   VALUES (s.id, s.column1, s.column2);

COMMIT;

• MERGE INTO specifies the target table
• USING specifies the source table or subquery
• ON defines the condition for matching rows
• WHEN MATCHED controls updates
• WHEN NOT MATCHED controls inserts

Preparing Sample Tables

We will create the employees table, insert some initial data, and then prepare a new table called employees_update to act as the source of new or changed data.

-- Create employees table
CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(10,2),
  department_id NUMBER
);

-- Insert sample employees
INSERT INTO employees VALUES (1, 'John', 'Smith', 8000, 10);
INSERT INTO employees VALUES (2, 'Alice', 'Johnson', 12000, 20);
INSERT INTO employees VALUES (3, 'Michael', 'Brown', 4500, 10);
INSERT INTO employees VALUES (4, 'Sophia', 'Davis', 6000, 30);
INSERT INTO employees VALUES (5, 'David', 'Wilson', 9500, 20);

-- Create source table
CREATE TABLE employees_update (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(10,2),
  department_id NUMBER
);

-- Insert some updated and new rows
INSERT INTO employees_update VALUES (1, 'John', 'Smith', 9500, 10);  
INSERT INTO employees_update VALUES (6, 'Emma', 'Taylor', 7000, 30);

COMMIT;

Simple MERGE Example

This MERGE updates John’s salary if his record exists and inserts Emma as a new employee since her ID does not exist in the target table.

MERGE INTO employees t
USING employees_update s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
   UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
   INSERT (employee_id, first_name, last_name, salary, department_id)
   VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

COMMIT;

MERGE with Multiple Column Updates

You can update more than one column when a match is found. For example, update both salary and department:

MERGE INTO employees t
USING employees_update s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
   UPDATE SET t.salary = s.salary,
              t.department_id = s.department_id
WHEN NOT MATCHED THEN
   INSERT (employee_id, first_name, last_name, salary, department_id)
   VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

COMMIT;

MERGE Using Subqueries

The source in a MERGE does not have to be a table. It can be a subquery that selects data from one or more tables. For example, only merge employees from department 30:

MERGE INTO employees t
USING (SELECT * FROM employees_update WHERE department_id = 30) s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
   UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
   INSERT (employee_id, first_name, last_name, salary, department_id)
   VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

COMMIT;

MERGE with DELETE Clause

MERGE also allows deleting rows if certain conditions are met during a match. For example, remove employees who have salaries less than 5000:

MERGE INTO employees t
USING employees_update s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
   UPDATE SET t.salary = s.salary
   DELETE WHERE t.salary < 5000
WHEN NOT MATCHED THEN
   INSERT (employee_id, first_name, last_name, salary, department_id)
   VALUES (s.employee_id, s.first_name, s.last_name, s.salary, s.department_id);

COMMIT;

MERGE with JSON Data in Oracle 23ai

If the target table has a JSON column, you can use MERGE to update or insert JSON values. For example, updating a product’s JSON data:

MERGE INTO products p
USING (SELECT 1 AS product_id, 
              '{"name":"Laptop Pro","specs":{"cpu":"Intel i9","ram":"32GB"}}' AS new_data
       FROM dual) s
ON (p.product_id = s.product_id)
WHEN MATCHED THEN
   UPDATE SET p.product_data = JSON_TRANSFORM(p.product_data,
                     REPLACE '$.specs.ram' = '32GB' RETURNING JSON)
WHEN NOT MATCHED THEN
   INSERT (product_id, product_data)
   VALUES (s.product_id, s.new_data);

COMMIT;

MERGE with Vector Data in Oracle 23ai

You can also use MERGE to manage vector embeddings. Suppose you want to update or insert document embeddings in the documents table:

MERGE INTO documents d
USING (SELECT 2 AS doc_id, 
              'Learn Oracle 23ai MERGE statement' AS content, 
              VECTOR('[0.3, 0.6, 0.8]') AS new_vec
       FROM dual) s
ON (d.doc_id = s.doc_id)
WHEN MATCHED THEN
   UPDATE SET d.embedding = s.new_vec
WHEN NOT MATCHED THEN
   INSERT (doc_id, content, embedding)
   VALUES (s.doc_id, s.content, s.new_vec);

COMMIT;

Best Practices for MERGE

1. Always define a clear ON condition to prevent unwanted updates
2. Use MERGE when synchronizing data between staging and main tables
3. Be cautious with DELETE inside MERGE, it can remove rows unexpectedly
4. For JSON updates, use JSON_TRANSFORM to change only specific keys
5. For vector updates, ensure correct syntax with VECTOR('...')

Conclusion

The MERGE statement in Oracle Database 23ai is an efficient way to synchronize data, replacing multiple INSERT and UPDATE statements with one clear command. From handling simple employee updates to working with modern JSON and vector data, MERGE gives developers a flexible tool for managing complex datasets. By using conditions, subqueries, and advanced features, you can keep your database consistent and optimized.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows