Table Aliases in DML: Cleaner UPDATE and DELETE Statements

Oracle 23ai allows table aliases in UPDATE and DELETE statements, making complex DML with subqueries and correlated references significantly more readable.

UPDATE with a table alias:

Before 23ai, the target table in UPDATE couldn’t easily have an alias in subquery references. Now:

UPDATE employees e
SET    e.salary = e.salary * 1.10
WHERE  e.department_id IN (
    SELECT d.department_id
    FROM   departments d
    WHERE  d.location_code = 'NYC'
);

The alias e is now cleanly scoped across the entire statement.

DELETE with a table alias:

DELETE FROM order_archive oa
WHERE  oa.archived_date < ADD_MONTHS(SYSDATE, -24)
AND    oa.customer_id IN (
    SELECT c.customer_id
    FROM   customers c
    WHERE  c.account_status = 'CLOSED'
);

Combining with direct joins (covered in Feb):

UPDATE employees e
JOIN   performance_reviews pr ON pr.employee_id = e.employee_id
SET    e.rating = pr.final_rating,
       e.last_review_date = pr.review_date
WHERE  pr.review_year = 2024
AND    e.status = 'ACTIVE';

While table aliases in DML were partially supported before (especially in correlated subqueries), 23ai formalizes and extends this capability to work cleanly with the new direct JOIN syntax. Together, they make multi-table DML in Oracle as readable as equivalent SELECT queries — which has always been the goal of SQL ergonomics.