sql server - SQL UPDATE CASE

Sql server - SQL UPDATE CASE

In SQL Server, you can use a CASE statement within an UPDATE query to conditionally update rows based on specific criteria. This allows you to modify data in a table based on conditions that you define. Here's how you can use CASE in an UPDATE statement:

Example Scenario

Let's say you have a table named Employees with columns EmployeeID, FirstName, LastName, Salary, and DepartmentID. You want to update the Salary column for employees based on their DepartmentID. For employees in the IT department (DepartmentID = 1), you want to increase their salary by 10%. For employees in the Sales department (DepartmentID = 2), you want to decrease their salary by 5%. All other employees' salaries remain unchanged.

Using CASE in UPDATE Statement

You can use the CASE statement within the UPDATE statement to apply different updates based on conditions:

UPDATE Employees SET Salary = CASE WHEN DepartmentID = 1 THEN Salary * 1.1 -- Increase by 10% for IT department WHEN DepartmentID = 2 THEN Salary * 0.95 -- Decrease by 5% for Sales department ELSE Salary -- No change for other departments END WHERE DepartmentID IN (1, 2); -- Update only for IT and Sales departments

Explanation

  • CASE WHEN ... THEN ... ELSE ... END: This construct evaluates each row and updates the Salary column based on the conditions specified.

  • SET Salary = ...: Updates the Salary column with the calculated value from the CASE statement.

  • WHERE DepartmentID IN (1, 2): Filters the rows to update only employees in the IT and Sales departments. Adjust this condition based on your specific filtering criteria.

Example Output

Assume the Employees table initially has data like this:

EmployeeIDFirstNameLastNameSalaryDepartmentID
1JohnDoe500001
2JaneSmith600002
3MarkJohnson450001
4SarahBrown550003
5DavidLee480002

After executing the UPDATE statement above:

EmployeeIDFirstNameLastNameSalaryDepartmentID
1JohnDoe550001
2JaneSmith570002
3MarkJohnson495001
4SarahBrown550003
5DavidLee480002

Considerations

  • Transaction Safety: Ensure proper transaction handling if needed, especially if updates affect critical data.

  • Performance: Consider indexing columns used in WHERE clause (DepartmentID in this case) for better query performance, especially on large datasets.

  • Data Consistency: Verify that the conditions in the CASE statement accurately reflect your business logic to avoid unintended updates.

By using CASE in an UPDATE statement in SQL Server, you can efficiently update rows based on conditional logic, making it a powerful tool for data manipulation tasks. Adjust the CASE conditions and SET clause according to your specific update requirements and table structure.

Examples

  1. Update column based on condition using CASE statement

    Description: Update a column in SQL Server based on a condition using a CASE statement.

    UPDATE your_table SET status = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE default_value END WHERE your_condition;

    Explanation:

    • Updates the status column in your_table based on conditions specified in the CASE statement.
    • condition1, condition2, and default_value are placeholders for actual conditions and values.
    • your_condition specifies the rows to be updated.

  2. Update multiple columns conditionally with CASE

    Description: Update multiple columns in SQL Server based on different conditions using a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 ELSE column1 END, column2 = CASE WHEN condition2 THEN value2 ELSE column2 END WHERE your_condition;

    Explanation:

    • Updates column1 and column2 in your_table based on different conditions using separate CASE statements for each column.
    • Each CASE statement checks a condition (condition1, condition2) and updates the column accordingly.

  3. Update column with NULL or default value using CASE

    Description: Update a column with NULL or a default value based on a condition using a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition THEN NULL ELSE default_value END WHERE your_condition;

    Explanation:

    • Sets column1 to NULL or default_value based on the specified condition using a CASE statement.
    • your_condition determines which rows are updated.

  4. Update column with dynamic value using CASE

    Description: Update a column with a dynamically determined value based on a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE default_value END WHERE your_condition;

    Explanation:

    • Updates column1 in your_table with values (value1, value2, or default_value) based on specified conditions using CASE.
    • your_condition specifies the rows to be updated.

  5. Update column based on multiple conditions with CASE

    Description: Update a column in SQL Server based on multiple conditions using a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 WHEN condition3 THEN value3 ELSE default_value END WHERE your_condition;

    Explanation:

    • Uses multiple WHEN clauses in the CASE statement to update column1 with different values (value1, value2, value3, or default_value) based on various conditions.
    • your_condition determines which rows are updated.

  6. Update column based on subquery result using CASE

    Description: Update a column in SQL Server based on the result of a subquery using a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN EXISTS (SELECT 1 FROM other_table WHERE condition) THEN value1 ELSE default_value END WHERE your_condition;

    Explanation:

    • Updates column1 in your_table based on the existence of a record in other_table satisfying condition.
    • Uses CASE to set column1 to value1 if the subquery returns any rows; otherwise, sets it to default_value.
    • your_condition specifies the rows to be updated.

  7. Update column based on aggregation result using CASE

    Description: Update a column in SQL Server based on an aggregation result using a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN COUNT(*) > 100 THEN 'High' WHEN COUNT(*) > 50 THEN 'Medium' ELSE 'Low' END WHERE your_condition;

    Explanation:

    • Updates column1 in your_table based on the count of rows meeting your_condition.
    • Uses CASE to assign values ('High', 'Medium', 'Low') based on the result of COUNT(*).
    • your_condition specifies the rows to be updated.

  8. Update column with computed value using CASE

    Description: Update a column with a computed value based on a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN expression1 WHEN condition2 THEN expression2 ELSE expression3 END WHERE your_condition;

    Explanation:

    • Updates column1 in your_table with a computed value determined by CASE.
    • expression1, expression2, and expression3 are placeholders for computed values based on conditions (condition1, condition2).
    • your_condition specifies the rows to be updated.

  9. Update column with multiple updates using CASE

    Description: Update multiple columns in SQL Server with different values based on a CASE statement.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 ELSE column1 END, column2 = CASE WHEN condition2 THEN value2 ELSE column2 END WHERE your_condition;

    Explanation:

    • Updates column1 and column2 in your_table based on different conditions using separate CASE statements for each column.
    • Each CASE statement checks a condition (condition1, condition2) and updates the respective column.

  10. Update column with nested CASE statement

    Description: Update a column in SQL Server using a nested CASE statement for more complex conditional logic.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN CASE WHEN nested_condition1 THEN value1 ELSE value2 END ELSE column1 END WHERE your_condition;

    Explanation:

    • Uses a nested CASE statement to provide more intricate conditional logic for updating column1.
    • nested_condition1, value1, and value2 are placeholders for additional conditions and values.
    • your_condition specifies the rows to be updated.

More Tags

pandas-datareader xls rss gnu-findutils graphql-spqr jenkins-email-ext android-softkeyboard mbunit fluent-assertions foreground-service

More Programming Questions

More Biology Calculators

More Fitness-Health Calculators

More Genetics Calculators

More Internet Calculators