SQL Basics
- What is SQL?
- Data Definition Language
- Data Manipulation Language
- Data Query Language
- Data Control Language
- Transaction Control Language
- Tables and Schemas
- Data Types
- Constraints
Querying and Filtering
Examples of SQL UPDATE statements along with descriptions:
Example 1: Updating a Single Column
UPDATE employeesSET 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 ordersSET 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 studentsSET grade = CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' WHEN score >= 60 THEN 'D' ELSE 'F' ENDWHERE 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.