In this blog, I’ll test executing UPDATE and DELETE commands with direct joins using the HR schema.
Prerequisites:
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';
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';