Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Update statement with CASE expression

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” Example:

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

First expression 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).

Code Sample:

Before executing update actual employee table data :


Select * from Employee 




---Case Statement with Update Query 

UPDATE Employee SET Gender =

 CASE Gender

  WHEN 1 THEN 'Male'

  WHEN 2 THEN 'Female'

  ELSE 'MiddleSex'

 END

 


After executing Update Query  Result is:

 

Select * from Employee 


  धन्यवाद मित्रानो ... 

ORDER BY clause with CASE Expressions(CASE STATEMENT)

ORDER BY clause with CASE Expressions(CASE STATEMENT): 

I was 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.

This experience I want to share in this post with example and code sample.

Searched CASE expression:

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Example:

In this example searched CASE expression used to decide order, for male all records are in descending order and for female ascending order.

Code sample:

SELECT * FROM dbo.Employee
ORDER BY 
 CASE WHEN Gender='Male' THEN EmployeeName END Desc,
 CASE WHEN Gender='Female' THEN Country END ASC




CASE STATEMENT/EXPRESSION in detail

CASE 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 three options

  1. Use Loop

  2. Use Cursor

  3. Use Case Statement

I prefer to go with Case statement because it provides better performance and easy to use.

Description:

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 

Case Statement Example:

SELECT EmployeeName,Country,CompanyPlant,

CASE  WHEN Gender =1 THEN 'Male'

      WHEN Gender =2 THEN 'Female'

      ELSE 'MiddleSex'

END  AS Gender FROM Employee  '