Oracle 23ai: Direct Joins for UPDATE and DELETE Statements

 

- Overview:

• Oracle 23ai now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause.
• These other tables can limit the rows changed or be the source of new values.
• Direct joins make it easier to write SQL to change and delete data.

In this blog, I'll test executing UPDATE and DELETE commands with direct joins using the HR schema.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• HR database schema.

Update with Direct Join

Use-Case: Increase the salaries of the Finance department by 5%. 

1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

 

2. Writing UPDATE in pre-23ai Oracle database using sub-query in the WHERE clause.

UPDATE employees e

SET e.salary=e.salary*1.05

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing UPDATE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

explain plan for 

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

Delete with Direct Join 

Use-Case: delete the employees of the Finance department.

 1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

2. Writing DELETE in pre-23ai Oracle database using sub-query in the WHERE clause.

DELETE employees e

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing DELETE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

DELETE employees e

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';