Oracle AI Database 26ai: 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 AI Database 26ai, achieving this required the simultaneous use of a SELECT statement because direct joins were not supported. However, Oracle AI Database 26ai 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