SQL UPDATE Statement


Examples of SQL UPDATE statements along with descriptions:

Example 1: Updating a Single Column

UPDATE employees
SET department = 'Marketing'
WHERE employee_id = 101;

Description: In this example, the UPDATE statement modifies the department column for the employee with employee_id 101 in the employees table. It sets the department to ‘Marketing’. This query is useful for updating specific attributes of a single record based on a unique identifier, such as an employee ID.

Example 2: Updating Multiple Columns

UPDATE orders
SET status = 'Shipped', ship_date = '2024-06-15'
WHERE order_id = 9876;

Description: Here, the UPDATE statement modifies two columns (status and ship_date) for the order with order_id 9876 in the orders table. It updates the status to ‘Shipped’ and sets the ship_date to ‘2024-06-15’. This type of query is common when multiple attributes of a record need to be updated simultaneously.

Example 3: Conditional Update with CASE Statement

UPDATE students
SET grade =
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END
WHERE exam_type = 'Final';

Description: In this example, the UPDATE statement uses a CASE statement within the SET clause to update the grade column of students based on their score in the students table. Depending on the value of score, the grade is assigned using conditional logic (CASE statement). This approach allows for dynamic updates based on specific conditions, making it useful for grading systems or any scenario where values need to be derived based on other columns.

Summary: SQL’s UPDATE statement is versatile, allowing for precise modifications of data within relational databases. Whether updating a single column based on a condition, modifying multiple columns in one go, or using conditional logic to update values dynamically, SQL provides robust capabilities for managing and manipulating data efficiently. These examples illustrate different scenarios where the UPDATE statement proves essential in maintaining data integrity and accuracy within database systems.