Oracle 23ai: New DELETE and MERGE Enhancements with Practical Examples

Introduction

For a long time, working with DELETE and MERGE in Oracle meant dealing with awkward syntax and unnecessary complexity, especially when joins or feedback from DML operations were involved. Oracle 23ai finally removes many of those pain points.

In this blog, I will look at three practical enhancements: join-based deletes using DELETE … FROM, capturing affected rows with DELETE … RETURNING, and retrieving results directly from MERGE operations using RETURNING.

DELETE … FROM 

For a long time, deleting rows based on conditions in another table was cumbersome in Oracle. You often had to write complicated EXISTS clauses or nested subqueries, which made the code harder to read and maintain. With Oracle 23ai, you can now use a direct FROM clause in your DELETE statement to join the target table with other tables. This aligns the syntax with how we write SELECT queries or UPDATE ... FROM statements, making the intent of your SQL clearer.

Example: Remove Employees in the IT Department

Suppose you want to delete all employees who work in the Shipping department. Previously, you might have had to write a subquery like DELETE FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT'). Now, you can do it more cleanly using a join:

Select e.employee_id,e.first_name, e.last_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id
  AND d.department_name = 'IT';

DELETE FROM employees e
FROM departments d
WHERE e.department_id = d.department_id
  AND d.department_name = 'IT';
Commit;

DELETE ... RETURNING

The RETURNING clause is a real game-changer when it comes to maintaining data integrity. In earlier versions of Oracle, you could use it, but in 23ai its usefulness really shines, especially during more complex deletions. Instead of running a separate SELECT to grab the values before you delete them, RETURNING lets you capture the data at the moment it’s being deleted. This makes it perfect for logging, archiving, or passing information back to your application immediately.

 Example: Deleting an Employee and Logging Their Info

Suppose you want to remove a specific employee from the database but also keep a record of their name and final salary for auditing purposes. With RETURNING, you can do this in one step:

-- Create a demo table
CREATE TABLE employees_demo (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50)
);

-- Insert a test row
INSERT INTO employees_demo VALUES (999, 'AMIR', 'KORDESTANI');
COMMIT;

-- Now delete with RETURNING 
SET SERVEROUTPUT ON;
DECLARE
    v_name       VARCHAR2(100);
    v_employee_id employees_demo.employee_id%TYPE;
BEGIN
    DELETE FROM employees_demo
    WHERE employee_id = 999
    RETURNING (first_name || ' ' || last_name), employee_id
    INTO v_name, v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Deleted Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: ' || v_employee_id);
END;
/

MERGE ... RETURNING

The MERGE statement lets you insert and update rows in a single operation. In earlier versions of Oracle, MERGE could feel like a black box: you wouldn’t know the final values of the affected rows without running an additional SELECT.

Oracle 23ai changes that with the RETURNING clause for MERGE. Now, whether a row is updated or newly inserted, you can immediately capture the resulting values. This is especially useful for logging, sequences, or auto-calculated columns.

Example: Upserting Employees and Capturing Their Final Bonus

Imagine you have a table of employees_demo and want to either update an existing employee’s bonus or add a new employee if they don’t exist. At the same time, you want to know the final bonus immediately. The MERGE … RETURNING statement lets you do this in a single step, capturing the result directly into a variable and displaying it.

The MERGE statement lets us either update or insert a row in one atomic operation in the follwoing example:

• USING (SELECT 101 AS emp_id, 10000 AS new_bonus FROM dual) defines the source data. Here, we want employee 101 to have a bonus of 10000.
• ON (e.employee_id = s.emp_id) specifies how to match rows in the target table with the source.
• WHEN MATCHED THEN UPDATE: If employee 101 already exists (Alice), her bonus_amount is updated to 10000.
• WHEN NOT MATCHED THEN INSERT: If employee 101 didn’t exist, a new row would be inserted with the provided values.
• The RETURNING e.bonus_amount INTO v_final_bonus clause immediately captures the resulting bonus after the MERGE.
• This works whether the row was updated or inserted, eliminating the need for a separate SELECT to find the final value.

-- Create a safe demo table for employees
CREATE TABLE employees_demo (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    hire_date     DATE,
    bonus_amount  NUMBER
);

-- Insert an initial row
INSERT INTO employees_demo VALUES (101, 'Amir', 'Kordestani', DATE '2025-01-01', 5000);
COMMIT;

SET SERVEROUTPUT ON;

DECLARE
    v_final_bonus employees_demo.bonus_amount%TYPE;
BEGIN
    MERGE INTO employees_demo e
    USING (SELECT 101 AS emp_id, 10000 AS new_bonus FROM dual) s
    ON (e.employee_id = s.emp_id)
    WHEN MATCHED THEN
        UPDATE SET e.bonus_amount = s.new_bonus
    WHEN NOT MATCHED THEN
        INSERT (employee_id, first_name, last_name, hire_date, bonus_amount)
        VALUES (s.emp_id, 'New', 'Hire', SYSDATE, s.new_bonus)
    RETURNING e.bonus_amount INTO v_final_bonus;

    -- Show the result
    DBMS_OUTPUT.PUT_LINE('Final bonus for employee ' || 101 || ': ' || v_final_bonus);
END;
/

Conclusion

Oracle 23ai is a real step forward for day-to-day SQL development. Enhancements like join-based deletes and RETURNING support for both DELETE and MERGE finally remove some long-standing pain points that many of us have worked around for years.

These improvements aren’t just about writing less code, they make SQL easier to read, easier to reason about, and safer to maintain. If you’re moving to Oracle 23ai or 26ai, it’s worth taking the time to revisit older DELETE and MERGE statements and refactor them using this newer syntax.