Updating Column Values and Deleting Rows

Objectives\n\n* The following goals are established for the management of table data:\n * Construct and execute an $UPDATE$ statement.\n * Construct and execute a $DELETE$ statement.\n * Construct and execute a query utilizing a subquery to update and delete data within a table.\n * Construct and execute a query involving a correlated subquery for updating and deleting table data.\n * Explain the influence of foreign-key and primary-key integrity constraints on $UPDATE$ and $DELETE$ statements.\n * Explain the specific purpose of the $FOR\\,UPDATE$ clause within a $SELECT$ statement.\n\n# Purpose of Database Management\n\n* In database environments, change is the only constant. While one might wish for tasks like cleaning or grading to remain permanent, data must be constantly managed.\n* The role of a Database Administrator ($DBA$) involves updating, inserting, deleting, and managing data records. Mastery of these commands allows a user to act as the $DBA$ of their own schema to maintain database integrity and relevance.\n\n# The UPDATE Statement\n\n* The $UPDATE$ statement is used specifically to modify existing rows within a table.\n* An $UPDATE$ statement requires four distinct values to execute correctly:\n * The name of the target table.\n * The name of the column or columns whose values are being modified.\n * A new value for each column being modified.\n * A condition ($WHERE$ clause) to identify which specific rows in the table should be modified.\n* Syntactic Recommendations: It is recommended that the $UPDATE$ statement be placed on its own line for clarity.\n* Example of a basic update: To change the phone number of a specific employee in the $copy\\_employees$ table, the following syntax is used:\n * $UPDATE\\,copy\\_employees\\,SET\\,phone\\,number\\,=\\,'123456'\\,WHERE\\,employee\\_id\\,=\\,303;$\n* Updating Multiple Columns and Rows: It is possible to modify several columns and several rows simultaneously. In an example where both the phone number and last name are changed for employees with an ID greater than or equal to $303$, the syntax is:\n * $UPDATE\\,copy\\_employees\\,SET\\,phone\\,number\\,=\\,'654321',\\,last\\_name\\,=\\,'Jones'\\,WHERE\\,employee\\_id\\,\\ge\\,303;$\n* Danger of Omitted Clauses: Extreme care must be taken when updating. If the $WHERE$ clause is omitted, every single row in the table will be updated with the new values. For instance, omitting the condition in the previous example would result in every employee in the table having the last name $'Jones'$ and the same phone number.\n\n# Updating with Subqueries\n\n* A new value for a column can be derived from the result of a single-row subquery.\n* Subquery Execution Process: The subquery executes first to retrieve a specific value, which is then passed to the outer $UPDATE$ statement as the new value for the designated column.\n* Example (Single Column): Changing the salary of employee $101$ to match the salary of employee $100$:\n * $UPDATE\\,copy\\_employees\\,SET\\,salary\\,=\\,(SELECT\\,salary\\,FROM\\,copy\\_employees\\,WHERE\\,employee\\_id\\,=\\,100)\\,WHERE\\,employee\\_id\\,=\\,101;$\n* Updating Two Columns with Two Subqueries: Multiple columns can be updated in one statement by writing a separate single-row subquery for each column.\n * Example: Updating the salary and $job\\_id$ of employee $206$ to match those of employee $205$:\n * $UPDATE\\,copy\\_employees\\,SET\\,salary\\,=\\,(SELECT\\,salary\\,FROM\\,copy\\_employees\\,WHERE\\,employee\\_id\\,=\\,205),\\,job\\_id\\,=\\,(SELECT\\,job\\_id\\,FROM\\,copy\\_employees\\,WHERE\\,employee\\_id\\,=\\,205)\\,WHERE\\,employee\\_id\\,=\\,206;$\n* Updating Across Different Tables: A subquery can retrieve data from one table to update a different table. For example, retrieving a salary from the original $employees$ table to update the $copy\\_employees$ table:\n * $UPDATE\\,copy\\_employees\\,SET\\,salary\\,=\\,(SELECT\\,salary\\,FROM\\,employees\\,WHERE\\,employee\\_id\\,=\\,205)\\,WHERE\\,employee\\_id\\,=\\,202;$\n\n# Correlated Subquery for Updates\n\n* Subqueries used in updates can be either standalone or correlated. In a correlated subquery, the update of a row in a table is based on a selection from that same table or a related table that references the outer query's row.\n* Implementation Example: Suppose a new column $department\\_name$ is added to the $copy\\_employees$ table and needs to be populated with data from the $departments$ table.\n * First, the table is altered: $ALTER\\,TABLE\\,copy\\_employees\\,ADD\\,(department\\_name\\,varchar2(30)\\,NOT\\,NULL);$\n * Then, the correlated subquery updates the name based on the matching $department\\_id$: $UPDATE\\,copy\\_employees\\,e\\,SET\\,e.department\\_name\\,=\\,(SELECT\\,d.department\\_name\\,FROM\\,departments\\,d\\,WHERE\\,e.department\\_id\\,=\\,d.department\\_id);$\n\n# The DELETE Statement\n\n* The $DELETE$ statement is used to remove existing rows from a table.\n* The statement requires two primary values:\n * The name of the table.\n * A condition identifying which rows are to be removed.\n* Example: To delete the record of an employee with ID $303$: $DELETE\\,FROM\\,copy\\_employees\\,WHERE\\,employee\\_id\\,=\\,303;$\n* Consequences of Omitting WHERE: If the $WHERE$ clause is omitted in a $DELETE$ statement, all rows in the table are deleted. No data will remain in the table, though the table structure itself remains.\n\n# Deleting with Subqueries\n\n* Subqueries can be nested within the $WHERE$ clause of a $DELETE$ statement to target rows based on complex criteria.\n* Example (Standard Subquery): Deleting all employees who belong to the $'Shipping'$ department:\n * $DELETE\\,FROM\\,copy\\_employees\\,WHERE\\,department\\_id\\,=\\,(SELECT\\,department\\_id\\,FROM\\,departments\\,WHERE\\,department\\_name\\,=\\,'Shipping');$\n* Example (Correlated Subquery with Aggregation): Deleting employees who work for a manager who manages fewer than $2$ employees using the $HAVING$ clause:\n * $DELETE\\,FROM\\,copy\\_employees\\,e\\,WHERE\\,e.manager\\_id\\,IN\\,(SELECT\\,d.manager\\_id\\,FROM\\,employees\\,d\\,HAVING\\,count(d.department\\_id)\\,<\\,2\\,GROUP\\,BY\\,d.manager\\_id);$\n\n# Integrity Constraints in DML\n\n* Definition: Integrity constraints are rules that ensure data quality and conformity. These rules are automatically verified whenever a Data Manipulation Language ($DML$) statement, such as $UPDATE$ or $DELETE$, is executed.\n* Behavior: If a statement violates any rule, the table is not updated, the transaction fails, and an error is returned.\n* NOT NULL Constraint Violations: If an $UPDATE$ statement attempts to set a column to $NULL$ via a subquery that returns no result, and that column has a $NOT\\,NULL$ constraint, an error occurs. For example, trying to fetch the last name of a non-existent employee ($ID\\,=\\,999$) to update employee $101$ will fail because $last\\_name$ cannot be null.\n* Primary Key and Foreign Key Constraints:\n * The $employees$ table has a foreign key on $department\\_id$ referencing the $departments$ table to ensure employees belong to valid departments.\n * In a scenario where $department\\_ids\\,10$ and $20$ exist but $15$ does not, attempting to set an employee's department to $15$ will result in a constraint error.\n* Behavior in Copied Tables: When creating a table using the $CREATE\\,TABLE\\,\\dots\\,AS\\,(SELECT\\,\\dots)$, the system copies the rows and $NOT\\,NULL$ constraints. However, it does not copy primary key or foreign key constraints. Consequently, updates on these copied tables will not trigger primary/foreign key integrity errors unless those constraints are manually added later.\n\n# Data Locking with the FOR UPDATE Clause\n\n* Standard $SELECT$ Behavior: By default, a $SELECT$ statement does not issue locks on the database rows. This minimizes database overhead.\n* Purpose: Sometimes, it is necessary to ensure that records returned by a query cannot be updated or deleted by other users while you are working on them.\n* Mechanism: The $FOR\\,UPDATE$ clause issues exclusive row-level locks on all rows returned by the $SELECT$ statement. These locks are held until a $COMMIT$ or $ROLLBACK$ command is issued.\n* APEX Limitation: The hosted instance of $APEX$ utilizes autocommit, which means row-level locks requested via $FOR\\,UPDATE$ will not be maintained as the transaction commits immediately.\n* Multiple Table Join Locking: If a $FOR\\,UPDATE$ clause is used in a query involving multiple tables, every row involved in the join from all tables will be locked.\n * Example: $SELECT\\,e.employee\\_id,\\,e.salary,\\,d.department\\_name\\,FROM\\,employees\\,e\\,JOIN\\,departments\\,d\\,USING\\,(department\\_id)\\,WHERE\\,job\\_id\\,=\\,'ST\\_CLERK'\\,AND\\,location\\_id\\,=\\,1500\\,FOR\\,UPDATE\\,ORDER\\,BY\\,e.employee\\_id;$\n * Result: If this query returns four rows (e.g., IDs $141$, $142$, $143$, and $144$), those specific rows in both the employee and department tables are locked for the current user.\n\n# Questions & Discussion\n\n* Evaluation of Integrity Constraint Scenarios: Consider three distinct statements and whether they return errors based on standard primary/foreign key constraints:\n 1. $UPDATE\\,employees\\,SET\\,department\\_id\\,=\\,15\\,WHERE\\,employee\\_id\\,=\\,100;$ (Error: Department $15$ does not exist).\n 2. $DELETE\\,FROM\\,departments\\,WHERE\\,department\\_id\\,=\\,10;$ (Potential Error: If employees are currently assigned to department $10$, this violates foreign key integrity).\n 3. $UPDATE\\,employees\\,SET\\,department\\_id\\,=\\,10\\,WHERE\\,department\\_id\\,=\\,20;$ (Success: Moves employees from one valid department to another valid department).", "title": "Updating Column Values and Deleting Rows"}