**Note**: Risk if duplicate names exist in the department table. - **Option 3**: Best practice—use specific identifiers such as unique department IDs to ensure accurate updates. # Making Changes Permanent - Changes made with UPDATE can be committed in the database using the COMMIT statement. - Changes remain local until COMMIT is executed, meaning other users cannot see them until confirmed. # Practical Applications of UPDATE Statement - **Moving Employees**: - Example: Moving an employee with the last name Zlaki to a new department. Steps include: 1. Identify the employee ID using SELECT statement. 2. Attempt to execute the UPDATE statement to change the department using department ID 909. - **Error Encountered**: - An error occurs if department ID 909 does not exist in the departments table. This highlights the enforcement of referential integrity by: - Foreign Key Constraints: Ensures that each department ID in the employees table corresponds to a valid ID in the departments table, preventing bad data entry. # Correcting an Update Operation - To successfully execute the UPDATE after an error: 1. Query the departments table to find the correct department ID. 2. Rerun the UPDATE statement with the valid department ID. 3. Use COMMIT to save the changes. # Important Considerations for Updates - Always include the WHERE clause to avoid indiscriminate updates affecting all records. - Use specificity in the WHERE clause to ensure correct updates. - After completing updates, utilize either COMMIT to save changes or ROLLBACK to undo if necessary. - Be aware of foreign key constraints that restrict column values in tables, ensuring data integrity and preventing the entry of erroneous data. # Conclusion - Understanding and properly utilizing the UPDATE statement along with foreign key constraints are crucial for maintaining accuracy and data integrity within relational databases, such as Oracle Database 19c. Editor em# Final Note on Referential Integrity - Understanding the structure and constraints imposed by the database schema is critical in executing UPDATE statements accurately without causing data integrity issues. \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... ... ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... ... ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... ... \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n ... ... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n \n ... ... ... \n \n \n \n \n \n \n ... ... \n \n \n \n \n \n \n \n \n \n \n ... ... \n \n ... \n \n \n ... \n ... \n ... \n | | | | | | | | |... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n ...... \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n ... \n \n \n \n \n \n \n \n \n \n \n \n... ... \n \n \n \n \n ... \n \n \n \n \n \n \n . . . ## Understanding Referential Integrity - Referencing integrity is a key aspect of relational database management, limiting the risk of erroneous data entries that could compromise data relationships across tables. ## Final Note - Proper usage of the UPDATE statement, along with considerations of referential integrity and effective use of foreign key constraints, is vital for ensuring data accuracy and reliability in a relational database environment such as Oracle Database 19c. \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n

Overview of the UPDATE Statement

  • The UPDATE statement is used for modifying existing records in a database table.

Purpose of the UPDATE Statement

  • After inserting rows into a table, changes may be required, such as:

    • Correcting addresses.

    • Filling in previously NULL columns with accurate data.

Syntax of the UPDATE Statement

  • Structure of the UPDATE statement includes the following components:

    • UPDATE Clause: Specifies the target table from which rows are to be updated.

    • SET Clause: Contains one or more column-value pairs:

    • Format: column_name = value

    • Value Options: Can be a constant, an expression, another column from the same row, or NULL.

    • WHERE Clause:

    • Contains a logical expression that determines which rows are to be updated.

    • Can include constants and column comparisons.

    • Similar to the conditions used in a SELECT statement or other DML statements.

Updating Rows

  • The UPDATE statement can affect:

    • Zero rows: If the WHERE clause does not match any existing rows, no rows will be updated (often unintentional).

    • One row: A specific row matching the WHERE clause will be updated.

    • Many rows: Multiple rows can be impacted if they meet the condition in the WHERE clause.

Example Scenario

  • Example Goal: Double the salaries of employees earning less than or equal to zero (hypothetical, as salaries should not be negative).

Row Selection Prior to Update

  • Use SELECT statement to browse department records:

    • Newly added departments are displayed at the end.

    • Identified need to fix a spelling error in the department name for ID 1001.

Options for Updating Rows

  • When correcting a row, there are several options for targeting the rows:

    • Option 1: Update using a general condition that could affect multiple records and create a risk of unintended modifications.

    • Option 2: Updating based on a substring (e.g., department names starting with