Oracle 23ai: Direct Joins for UPDATE and DELETE Statements

To delete or update records in a table, it is often necessary to join that table with other tables to determine which records should be deleted or updated. Prior to Oracle 23ai, achieving this required the simultaneous use of a SELECT statement because direct joins were not supported. However, Oracle 23ai introduces this feature, allowing direct joins in such scenarios.

For example, the following query demonstrates an update operation where a join between the employees and departments tables identifies which records in the employees table should be updated:

SQL> UPDATE employees e
  2     SET e.salary = e.salary * 2
  3     FROM departments d
  4   WHERE d.department_id = e.department_id
  5     AND d.department_name = 'IT';
5 rows updated

This feature can also be used for deleting records. Consider the following example:

SQL> delete employees e
  2     from departments d
  3   where d.department_id = e.department_id
  4     and d.department_name = 'IT'
  5     and e.employee_id!=d.manager_id;
4 rows deleted