If Statement In An Update

Facebooktwittergoogle_plusredditpinterestlinkedinmail

If you are updating multiple rows in one statement, but only want to update a value in a column if a condition is satisfied, then you need to perform some sort of if logic inside your update. Here’s a straight forward way of doing this.

In the example below, I am just going to use 2 temp table variables. I’ll update the @SourceTable with the data from the @UpdateTable… but only where the UpdateFlag field is set to 1.

 
In the example above, you can see that the AnimalName is only updated when the UpdateFlag column is set to 1. The 2nd record “Cat” is not updated with the value “Cow” because it has a 0 for the UpdateFlag. This is done using a case statement on the SET operation. The example above is very simple. This case logic in the SET operation becomes much more helpful in more complex queries.

Leave a Comment

CommentLuv badge