MySQL: Updating the Configuration of a MySQL DB System on OCI
Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to ... Read More
Learn more about why Eclipsys has been named the 2023 Best Workplaces in Technology and Ontario, Certified as a Great Place to Work in Canada and named Canada’s Top SME Employer!
Learn more!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 23c 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-23c 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 23c 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';
Overview: A MySQL configuration is a collection of variables that define the operation of a MySQL DB system. It is analogous to ... Read More
Overview: Oracle 23c introduces the use of SUM and AVG functions with INTERVAL datatype This enhancement makes it easier to calculate totals and ... Read More