Oracle 23ai: Direct Joins for UPDATE and DELETE Statements – Eclipsys

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-23c 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. Write 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 the 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 the 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';