T-sql: Using a CASE in an UPDATE statement to update certain columns depending on a condition

sql

dynamic-sql

update-statements

error-handling

byNikita Barsukov·Jan 8, 2025

Optimize your update queries in T-SQL using CASE. Here's the concise form:

UPDATE MyTable
SET ColumnA = CASE WHEN ConditionA THEN NewValueA ELSE ColumnA END,  -- No change, no pain
    ColumnB = CASE WHEN ConditionB THEN NewValueB ELSE ColumnB END  -- B stands for Better
WHERE RowFilter;

The provided query updates ColumnA and ColumnB based on ConditionA and ConditionB, maintaining original values when conditions are unmet.

Ensuring precise updates

CASE in an UPDATE statement provides precision equivalence to a skilled surgeon's hands. Each condition-action pair fine-tunes data modification.

Concurrency in context

In high transactional volume environments, data integrity is critical. Fortify your updates with transactions and error handling to fend off confounding concurrency issues.

Complex scenarios: Dynamic SQL

When conditional dynamics push beyond the CASE solution's limits, join forces with dynamic SQL. Using subqueries, joins, or dynamic SQL can help build a robust UPDATE statement.

The safety net: Backup and Rollback

Be the superhero your data needs. Backup your table prior to significant updates and have a rollback strategy hidden in your utility belt to combat accidental data spoils.

Harnessing Dynamic SQL: Beyond CASE

As the plot thickens, and conditions multiply or seem too complex, flexible friend Dynamic SQL takes centre stage, building the query string based on runtime information.

Test drive: Query validation

Before hitting the updateution button, buckle up and take your queries for a test drive. Ensure all road signs (conditions) lead to the right destination (expected results).

The hidden effects: Triggers and constraints

In the realm of data, triggers and check constraints lurk, potentially interacting with your update logic. Consider these hidden effects to keep your query drama-free.