SQL Server 2016 Hosting – Using CASE Statements In A SQL UPDATE Query

Peter Cole | March 13, 2018 | SQL 2016 Hosting | No Comments

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,

The simple CASE expression compares an expression to a set of simple expressions to determine the result.
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

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

SQL

    Insert Values in to table.
        INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)  
        VALUES('Kevin', 'Peter', 'MH', 15.00,'M')  
        INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)  
        VALUES('Joseph', 'Peter', 'MP', 5.00 ,'M')  
        INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)  
        VALUES('Mark', 'Peter', 'MP', 20.00 ,'M')  
    SQL

Insert Values in to table.

INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)

VALUES('Kevin', 'Peter', 'MH', 15.00,'M')

INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)

VALUES('Joseph', 'Peter', 'MP', 5.00 ,'M')

INSERT INTO dbo.Customer (FirstName, LastName, StateCode, PayRate,Gender)

VALUES('Mark', 'Peter', 'MP', 20.00 ,'M')

SQL

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

     UPDATE Customer  
        SET StateCode = CASE StateCode  
        WHEN 'MH' THEN 'Richard'  
        WHEN 'MP' THEN 'Tom'  
        ELSE NULL  
        END

UPDATE Customer

SET StateCode = CASE StateCode

WHEN 'MH' THEN 'Richard'

WHEN 'MP' THEN 'Tom'

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.

SQL
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.

Related Posts

About The Author

Pete