UPDATE

The SQL UPDATE command is used to modify existing records in a table. It allows developers and database administrators to make changes to one or multiple rows based on specified conditions. This guide explains the different ways to use the UPDATE command and what happens internally during execution.

What is the UPDATE Command in SQL?

The UPDATE command is a Data Manipulation Language (DML) operation that modifies existing data in a database table. It can update one or more columns based on specific conditions or apply changes to the entire table.

Types of UPDATE Commands in SQL

1. Update Specific Rows

The most common use of the UPDATE command is to modify specific rows in a table based on a condition.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET salary = 75000
WHERE id = 101;

What Happens Internally:

1. Parse and Validate: The database parses the query and validates the table name, columns, and data types.
2. Lock Rows: A lock is placed on the rows that match the condition to prevent other operations from modifying them simultaneously.
3. Apply Changes: The specified values are updated in the data pages.
4. Log Changes: The operation is recorded in the transaction log for recovery and rollback purposes.
5. Commit Changes: The updates are finalized when the transaction is committed.

2. Update All Rows in a Table

When no WHERE clause is provided, all rows in the table are updated.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...;

Example:

UPDATE employees
SET department = 'General';

What Happens Internally:

1. Lock Entire Table: A lock is placed on the entire table since all rows are affected.
2. Update in Batches: Changes are applied in batches, depending on the database system’s configuration.
3. Log and Commit: Each change is logged, and the updates are committed upon completion.

3. Update Using a Subquery

Use a subquery to calculate or fetch the values for the update.

Syntax:

UPDATE table_name
SET column1 = (SELECT value FROM another_table WHERE condition)
WHERE condition;

Example:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'HR')
WHERE department = 'HR';

What Happens Internally:

1. Execute Subquery: The subquery is executed first to retrieve the required values.
2. Validate Results: Ensures that the subquery returns a single value for each row to be updated.
3. Apply Updates: Updates are applied row by row using the subquery results.

4. Update Multiple Tables with JOIN

Modify data in one table based on data from another table using a JOIN.

Syntax:

UPDATE table1
SET table1.column1 = value
FROM table1
JOIN table2 ON table1.common_column = table2.common_column
WHERE condition;

Example (SQL Server):

UPDATE e
SET e.salary = d.average_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.department = 'IT';

What Happens Internally:

1. Perform Join Operation: The database executes the join to match rows from the two tables.
2. Identify Rows to Update: The matching rows are filtered based on the WHERE clause.
3. Apply Changes: Updates are applied to the target table.

5. Conditional Updates with CASE

Use the CASE statement to apply conditional logic during updates.

Syntax:

UPDATE table_name
SET column1 = CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE value3
END
WHERE condition;

Example:

UPDATE employees
SET salary = CASE
  WHEN department = 'HR' THEN 70000
  WHEN department = 'IT' THEN 90000
  ELSE 60000
END;

What Happens Internally:

1. Evaluate CASE Conditions: For each row, the CASE statement is evaluated to determine the appropriate value.
2. Apply Updates: The computed values are used to update the specified rows.

Internal Mechanics of the UPDATE Command

1. Parsing and Query Planning

• The SQL query is parsed, and an execution plan is generated to determine the most efficient way to locate and update the rows.

2. Row Locking

• Locks are placed on affected rows or the entire table (depending on the scope) to ensure data consistency.

3. Data Modification

• The database locates the data pages where the rows are stored and modifies the necessary columns.

4. Index Updates

• If any indexed columns are updated, the database updates the associated index entries.

5. Transaction Logging

• Changes are recorded in the transaction log, including the original and new values, for rollback and recovery.

6. Commit or Rollback

• Once the update is complete, changes are either committed to make them permanent or rolled back in case of an error or transaction failure.

Best Practices for Using the UPDATE Command

1. Always Use a WHERE Clause (When Possible):
   • Avoid accidental updates to all rows by including a WHERE clause.
2. Batch Updates for Large Tables:
   • Break updates into smaller batches to avoid locking issues and transaction log overflow.
   UPDATE employees SET salary = 75000 WHERE department = 'HR' LIMIT 1000;
3. Test Updates in Transactions:
   • Use transactions to test updates and rollback changes if needed.
   • BEGIN TRANSACTION;
   • UPDATE employees SET salary = 80000 WHERE department = 'Finance';
   • ROLLBACK; -- If necessary
4. Index Considerations:
   • Be mindful of indexed columns, as updating them can have performance implications.
5. Use Joins and Subqueries Wisely:
   • Optimize joins and subqueries to avoid performance bottlenecks.

Conclusion:
The SQL UPDATE command is a powerful tool for modifying existing data. By understanding its various methods and internal workings, you can ensure efficient and error-free updates. Whether updating specific rows, using subqueries, or joining tables, always prioritize performance and data integrity.