sql server - Using CASE within a single query to update multiple columns in SQL table

Sql server - Using CASE within a single query to update multiple columns in SQL table

In SQL Server, you can use the CASE expression within an UPDATE statement to conditionally update multiple columns based on different criteria. Here's how you can do it:

UPDATE YourTable SET Column1 = CASE WHEN Condition1 THEN Value1 WHEN Condition2 THEN Value2 ELSE Column1 -- Keep the current value if no condition is met END, Column2 = CASE WHEN Condition3 THEN Value3 WHEN Condition4 THEN Value4 ELSE Column2 -- Keep the current value if no condition is met END, -- Repeat for other columns as needed WHERE -- Add your WHERE clause to specify which rows to update

In this example:

  • YourTable is the name of your table.
  • Column1 and Column2 are the columns you want to update.
  • Condition1, Condition2, Condition3, and Condition4 are the conditions that determine which value to update each column with.
  • Value1, Value2, Value3, and Value4 are the values to update each column with when the corresponding condition is met.

Make sure to adjust the table name, column names, conditions, and values according to your specific requirements. You can add as many CASE expressions as needed to update multiple columns within a single UPDATE statement.

Examples

  1. SQL Server update query with CASE statement for multiple columns:

    Description: This query demonstrates how to use a single SQL UPDATE statement with a CASE expression to conditionally update multiple columns based on different criteria.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE column1 END, column2 = CASE WHEN condition3 THEN value3 WHEN condition4 THEN value4 ELSE column2 END, -- Add more columns as needed WHERE your_condition;

  2. SQL Server update query with CASE for NULL values:

    Description: This query demonstrates how to use a CASE statement within an UPDATE query to handle NULL values in multiple columns.

    UPDATE your_table SET column1 = CASE WHEN column1 IS NULL THEN value1 ELSE column1 END, column2 = CASE WHEN column2 IS NULL THEN value2 ELSE column2 END, -- Add more columns as needed WHERE your_condition;

  3. SQL Server update query with nested CASE statements:

    Description: This query showcases the use of nested CASE statements within an UPDATE query to update multiple columns based on complex conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN CASE WHEN nested_condition1 THEN value1 ELSE column1 END ELSE column1 END, -- Add more nested CASE statements for other columns WHERE your_condition;

  4. SQL Server update query with CASE for multiple conditions:

    Description: This query illustrates how to use a single UPDATE statement with multiple CASE expressions to update columns based on different conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE column1 END, column2 = CASE WHEN condition3 THEN value3 WHEN condition4 THEN value4 ELSE column2 END, -- Add more columns and conditions as necessary WHERE your_condition;

  5. SQL Server update query with CASE for string values:

    Description: This query demonstrates how to use a CASE statement within an UPDATE query to update multiple columns with string values based on specific conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN 'String1' WHEN condition2 THEN 'String2' ELSE column1 END, column2 = CASE WHEN condition3 THEN 'String3' WHEN condition4 THEN 'String4' ELSE column2 END, -- Add more columns and conditions as needed WHERE your_condition;

  6. SQL Server update query with CASE for numeric values:

    Description: This query showcases how to use a CASE statement within an UPDATE query to update multiple columns with numeric values based on specific conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN 1 WHEN condition2 THEN 2 ELSE column1 END, column2 = CASE WHEN condition3 THEN 3 WHEN condition4 THEN 4 ELSE column2 END, -- Add more columns and conditions as necessary WHERE your_condition;

  7. SQL Server update query with CASE for date values:

    Description: This query illustrates how to use a CASE statement within an UPDATE query to update multiple columns with date values based on specific conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN '2024-06-15' WHEN condition2 THEN '2024-06-16' ELSE column1 END, column2 = CASE WHEN condition3 THEN '2024-06-17' WHEN condition4 THEN '2024-06-18' ELSE column2 END, -- Add more columns and conditions as needed WHERE your_condition;

  8. SQL Server update query with CASE for boolean values:

    Description: This query demonstrates how to use a CASE statement within an UPDATE query to update multiple columns with boolean values based on specific conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN 1 WHEN condition2 THEN 0 ELSE column1 END, column2 = CASE WHEN condition3 THEN 1 WHEN condition4 THEN 0 ELSE column2 END, -- Add more columns and conditions as necessary WHERE your_condition;

  9. SQL Server update query with CASE for different data types:

    Description: This query showcases how to use a CASE statement within an UPDATE query to update multiple columns with values of different data types based on specific conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN 'String' WHEN condition2 THEN 123 ELSE column1 END, column2 = CASE WHEN condition3 THEN 'Another String' WHEN condition4 THEN 456 ELSE column2 END, -- Add more columns and conditions as needed WHERE your_condition;

  10. SQL Server update query with CASE for multiple columns and different conditions:

    Description: This query demonstrates a comprehensive example of using a CASE statement within an UPDATE query to update multiple columns with different conditions.

    UPDATE your_table SET column1 = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE column1 END, column2 = CASE WHEN condition3 THEN value3 WHEN condition4 THEN value4 ELSE column2 END, -- Add more columns and conditions as necessary WHERE your_condition;

More Tags

element-ui days ipad hibernate-entitymanager backgroundworker uialertview autoscroll spring-integration access-control oc4j

More Programming Questions

More Mixtures and solutions Calculators

More Financial Calculators

More Organic chemistry Calculators

More Gardening and crops Calculators