Direct Joins for UPDATE and DELETE in Oracle 23ai Database

In Oracle 23ai you can now update one table using values from another and limit the rows changed in DELETE and UPDATE

This makes the database more developer friendly and increases SQL standard support and provides parity with other non-Oracle systems.

Removing the job_history rows for all employees currently in department 10:

DELETE hr.job_history h
FROM hr.employees e
WHERE e.employee_id = h.employee_id
AND e.department_id = 10;

Setting the employee’s current salary to the max salary value in the JOBS table for their job:

UPDATE hr.employees e
SET e.salary = j.max_salary
FROM hr.jobs j
WHERE e.job_id = j.job_id;