Using CASE Statements In A SQL UPDATE Query

In some cases we need to select and modify the record based on specific conditions. So instead of using cursor or looping, we can use case CASE expression. CASE statement works like IF-THEN-ELSE statement.

I have SQL server Table in which there is column that I wanted to update according to a existing column value that is present in current row. In this scenario, we can use CASE expression. CASE expression is used for selecting or setting a new value from input values.

The CASE expression has two formats,

1. The simple CASE expression compares an expression to a set of simple expressions to determine the result.
2. The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Let’s have a look at syntax and example,

Syntax

CASE column_name
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END

Example

• Create SQL table that contains the below columns. We will use State code column for CASE expression
  
  
  

• Insert Values in to table. 
  

  INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
  VALUES('Sagar', 'Shinde', 'MH', 15.00,'M')
  INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
  VALUES('Swapnil', 'Shinde', 'MP', 5.00 ,'M')
  INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)
  VALUES('Pankaj', 'Shinde', 'MP', 20.00 ,'M')

  
  

• Let’s use CASE expression to update state c9de column value. In above table, I want to change state value i.e. MH to ‘Maharastra’ and ‘MP’ to ‘Madhya Pradesh.
  

  UPDATE Customer
  SET StateCode = CASE StateCode
  WHEN 'MH' THEN 'Maharastra'
  WHEN 'MP' THEN 'Madhya Pradesh'
  ELSE NULL
  END

Result

Please check below result screen. We have updated state code column using CASE expression. it is checked existing column and then updated.

Conclusion

I hope that you will get an idea about how to use CASE expression. Enjoy!

I hope you will enjoy these tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, questions, or comments about this article are always welcome.