Oracle 23ai: New UPDATE Statement Features and Practical Examples

 

Introduction

Oracle Database 23ai brings meaningful improvements to the UPDATE statement, making everyday data changes simpler and more intuitive. Long-standing limitations—such as complex join updates and extra queries to retrieve updated values—are now addressed with cleaner, more expressive SQL. Features like UPDATE … FROM enable direct join-based updates, while UPDATE … RETURNING allows immediate access to modified data. Native BOOLEAN support and the DEFAULT ON NULL clause further reduce workarounds and conditional logic. Together, these enhancements help developers write clearer, safer, and more maintainable UPDATE statements that better reflect real-world data operations.

1. UPDATE … FROM (Direct Join Support)

The “Finally!” feature for cleaner SQL

For years, updating a table based on another table’s data required tricky correlated subqueries or using MERGE. With Oracle 23ai, you can now join tables directly in an UPDATE statement using a FROM clause. This brings Oracle closer to PostgreSQL and SQL Server syntax, making the code far easier to read and maintain.

Example: Give a 10% raise to all employees in the Executive department.

Here, the UPDATE … FROM syntax allows you to reference the departments table directly. Oracle automatically joins the tables based on the condition in the WHERE clause. This eliminates the need for subqueries and ensures that only employees in the 'Executive' department get their salaries updated.

UPDATE employees e
SET e.salary = e.salary * 1.10
FROM departments d
WHERE e.department_id = d.department_id
  AND d.department_name = 'Executive';
commit;

2. UPDATE … RETURNING Old and New Values

Previously, the RETURNING clause in Oracle only allowed you to capture the new values after an update. If you wanted the previous state, you had to perform a separate SELECT, which added complexity and risk of inconsistencies. With Oracle 23ai, the introduction of the OLD and NEW keywords allows you to capture both the previous and updated values in a single, atomic operation. This makes it much easier to build audit logs, real-time notifications, or application logic that reacts to changes in data.

Example: Capture a salary change for a specific employee.

In this example, Oracle updates the employee’s salary by 500 and simultaneously stores the previous salary in :v_old_sal and the new salary in :v_new_sal. This eliminates the need for a separate query to fetch the old value.

SET SERVEROUTPUT ON;

DECLARE
    v_old_sal employees.salary%TYPE;
    v_new_sal employees.salary%TYPE;
BEGIN
    -- Update and capture old and new salary
    UPDATE employees
    SET salary = salary + 500
    WHERE employee_id = 101
    RETURNING OLD salary, NEW salary
    INTO v_old_sal, v_new_sal;

    -- Print the results
    DBMS_OUTPUT.PUT_LINE('Old Salary: ' || v_old_sal);
    DBMS_OUTPUT.PUT_LINE('New Salary: ' || v_new_sal);
END;
/

3. Native BOOLEAN Support in UPDATE

For decades, Oracle developers had to simulate boolean values using CHAR(1) or NUMBER(1) columns, often combined with check constraints to enforce TRUE/FALSE logic. Oracle 23ai introduces a native BOOLEAN data type, allowing you to use TRUE, FALSE, and NULL directly in your UPDATE statements. This makes your schema more intuitive, simplifies logic, and improves code readability.

Example: Flagging employees who joined before 2010 as "Legacy" staff.

In this example, the new is_legacy BOOLEAN column allows you to mark employees hired before 2013 directly with TRUE. There’s no need for CHAR or NUMBER workarounds, and querying is straightforward using TRUE/FALSE conditions. 

-- Add a boolean column
ALTER TABLE employees ADD is_legacy BOOLEAN;

-- Update the flag for legacy employees
UPDATE employees
SET is_legacy = TRUE
WHERE hire_date < DATE '2013-01-01';

-- Query using the boolean column
SELECT last_name
FROM employees
WHERE is_legacy = TRUE;

4. DEFAULT ON NULL for UPDATE

The DEFAULT ON NULL clause, originally available for INSERT statements, and now It has been extended to UPDATE in Oracle 23ai. This means that if an application attempts to set a column to NULL, Oracle will automatically replace it with the column’s defined default value. This feature helps maintain data integrity without the need for complex triggers or additional checks, ensuring that critical columns never end up with invalid or missing data.

Example: Prevent an employee’s bonus from ever becoming NULL.

In this example, the commission_pct column is protected against accidental NULL updates. Even if an application sends a NULL value, Oracle automatically applies the default (0 in this case).

-- Enable DEFAULT ON NULL for the column
ALTER TABLE employees 
MODIFY commission_pct DEFAULT ON NULL for Insert and update 0;

-- Attempt to set commission to NULL
UPDATE employees SET commission_pct = NULL WHERE employee_id = 999;
commit;

-- You will see 0 instead of NULL!
SELECT employee_id, first_name,last_name, commission_pct FROM employees WHERE employee_id = 999;

Conclusion

The UPDATE enhancements in Oracle 23ai significantly modernize one of the most commonly used DML operations. By simplifying join-based updates, capturing updated values in a single step, supporting BOOLEAN columns, and handling NULL assignments more intelligently, Oracle removes much of the complexity that developers have worked around for years. These features improve readability, reduce boilerplate code, and lower the risk of errors in data modification logic. When combined, they make UPDATE statements more expressive and aligned with modern SQL practices, reinforcing Oracle 23ai’s focus on developer productivity and cleaner database design.