3.2 Updating Data: UPDATE

The UPDATE statement in SQL is used to modify existing data in a table. This operation is essential for maintaining data accuracy and keeping databases up to date.

3.2.1 Overview of UPDATE

• Purpose: To update one or more rows in a table based on specified conditions.
• Syntax:

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

  • table_name: The table where the update is applied.
  • SET: Specifies the column(s) to update and their new values.
  • WHERE: Filters the rows to be updated (optional but highly recommended to avoid unintentional updates).

3.2.2 Key Points

1. Updating Specific Rows:
   Use the WHERE clause to specify which rows to update. Without a WHERE clause, all rows in the table will be updated.
   • Example: Update the salary of an employee with ID = 101:

     UPDATE employees
     SET salary = 60000
     WHERE id = 101;
     

2. Updating Multiple Columns:
   You can update multiple columns in a single query by separating assignments with commas.
   • Example: Update the department and salary of an employee:

     UPDATE employees
     SET department = 'Finance', salary = 65000
     WHERE id = 102;
     

3. Updating All Rows:
   If no WHERE clause is specified, the changes will apply to all rows in the table. Use caution with this approach.
   • Example: Increase all employee salaries by 5%:

     UPDATE employees
     SET salary = salary * 1.05;
     

3.2.3 Conditional Updates

SQL allows you to use conditions to perform updates based on specific criteria:

1. Based on a Comparison:
   • Example: Set all employees in the "Sales" department to a new commission rate:

     UPDATE employees
     SET commission = 0.10
     WHERE department = 'Sales';
     

2. Using Calculations:
   • Example: Add a 10% bonus to employees earning less than ₹50,000:

     UPDATE employees
     SET salary = salary + (salary * 0.10)
     WHERE salary < 50000;
     

3. With Subqueries:
   Subqueries can fetch values dynamically to be used in an update.
   • Example: Match the department manager's salary to the average salary in the department:

     UPDATE employees
     SET salary = (SELECT AVG(salary) 
                   FROM employees AS e 
                   WHERE e.department = employees.department)
     WHERE role = 'Manager';
     

3.2.4 Best Practices

1. Use a WHERE Clause: Always include a WHERE clause unless you're intentionally updating all rows.
2. Preview Before Applying: Run a SELECT query with the same WHERE condition to verify which rows will be updated.
   • Example:

     SELECT * 
     FROM employees
     WHERE department = 'Sales';
     

3. Backup Critical Data: Before making bulk updates, create a backup of the table.
4. Use Transactions: For critical or large updates, wrap them in transactions to ensure atomicity.
   • Example:

     BEGIN TRANSACTION;
     UPDATE employees
     SET salary = salary * 1.10
     WHERE department = 'Engineering';
     ROLLBACK; -- Reverts the changes if needed
     COMMIT;   -- Finalizes the changes
     

3.2.5 Real-World Examples

1. Correcting Data: Update a customer's phone number:

   UPDATE customers
   SET phone = '9876543210'
   WHERE customer_id = 123;
   

2. Promotions and Adjustments: Give all employees in the "HR" department a 7% raise:

   UPDATE employees
   SET salary = salary * 1.07
   WHERE department = 'HR';
   

3. Marking Inactive Records: Mark customers who haven't made a purchase in the last year as "inactive":

   UPDATE customers
   SET status = 'inactive'
   WHERE last_purchase_date < '2023-12-31';
   

4. Resetting Data: Reset all users' login attempts to 0:

   UPDATE users
   SET login_attempts = 0;
   

3.2.6 Risks of Improper Use

1. Updating Without a WHERE Clause:
   Running the following query updates all rows in the employees table:

   UPDATE employees
   SET salary = 50000; -- Caution: Applies to all rows!
   

2. Logical Errors in Conditions:
   Using a wrong condition may update unintended rows. Always verify conditions with a SELECT statement first.

Conclusion

The UPDATE statement is a powerful tool for modifying data in SQL databases. By mastering its syntax and best practices, you can ensure data consistency, minimize risks, and efficiently maintain your database's accuracy and relevance.