I was searching tutorials and some
code examples on how to use “Update statement with case expression”.
I had never tried this before and I am
quite happy that it works. With my first try I successfully written T-SQL query
that updates records using case expressions and this experience I want to share
in this post.
What is Case Expression?
Case Expression evaluates a list of
conditions and returns one of multiple possible result expressions.
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.
“Update statement with case expression”
In following example I updated
employee gender using simple case expression (equality check), When Gender
value is “1” Update to “Male”, When “2” Update to “Female” for other values
Update to “ MiddleSex”
compares with when clause expression, If these expressions are equivalent, the
expression in the THEN clause will be returned (“Male” in this example), else control
passed to next when clause expression if exist (“Female” in this example).
If no equivalent
expression in when clause then controlled passed to else clause and returns
else clause expression value (“ MiddleSex” in this example).
Before executing update actual employee table data :
Select * from Employee
Statement with Update Query
UPDATE Employee SET Gender =
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
After executing Update Query Result is:
Select * from Employee
धन्यवाद मित्रानो ...
ORDER BY clause
with CASE Expressions(CASE STATEMENT):
googling some code examples on how to use “ORDER BY clause with CASE
Expressions (CASE STATEMENT)”, with some googling information I tried following
t-sql code and that worked for me.
I want to share in this post with example and code sample.
Searched CASE expression:
CASE expression evaluates a set of Boolean expressions to determine the result.
In this example
searched CASE expression used to decide order, for male all records are in
descending order and for female ascending order.
SELECT * FROM dbo.Employee
CASE WHEN Gender='Male' THEN EmployeeName END Desc,
CASE WHEN Gender='Female' THEN Country END ASC
STATEMENT/EXPRESSION in detail:
In some cases you want to retrieve data/records and update
those records based
On some condition. To satisfy my above requirement we have
1. Use Loop
2. Use Cursor
3. Use Case
I prefer to go with Case statement because it provides
better performance and easy to use.
Evaluates a list of conditions and returns one of multiple
possible result expressions. CASE STATEMENT/EXPRESSION can be used in any
statement or clause that allows a valid expression. Such as SELECT, UPDATE,
DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and
HAVING, and inside of built-in functions.
Types of Case statement :
1. Simple CASE Expression more info...http://www.codechef4u.com/post/2015/04/08/simple-case-expression-case-statement
2. Searched CASE expression
Case Statement Example:
CASE WHEN Gender =1 THEN 'Male'
Gender =2 THEN 'Female'
END AS Gender FROM Employee '