Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

CASE Statement in Update Queries

September 7 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

We can implement CASE statement in Update Queries. Instead of multiple update queries, you can implement multile condition by CASE statement to update a table or get the customized result.

Ex : We have a table called Tmp_Tab with column as (PRIORITY,IsUpdated ,IsNeedtoDelete)

Current Data In Tmp_Tab

PRIORITY       IsUpdated       IsNeedtoDelete
HIGH               YES               NO
LOW                NO                1
HIGH               0                 YES
0                  NO                1


Execute the Below Update  statement :

UPDATE Tmp_Tab SET PRIORITY =
                        (CASE
                            WHEN PRIORITY = 'HIGH' THEN '0'
                            WHEN PRIORITY = 'MEDIUM' THEN '1'
                            WHEN PRIORITY = 'LOW' THEN '2'
                            ELSE PRIORITY  END
                             ),
                      IsPremium =
                        (CASE
                            WHEN IsUpdated   = 'YES' THEN '1'
                            WHEN IsUpdated   = 'NO' THEN '0'
                            ELSE IsUpdated   END
                             ),
                      IsSubscription =
                        (CASE
                            WHEN IsNeedtoDelete = 'YES' THEN '1'
                            WHEN IsNeedtoDelete = 'NO' THEN '0'
                            ELSE IsNeedtoDelete END
                             )



After Execution of the Query

PRIORITY               IsUpdated               IsNeedtoDelete
0                        1                          0
2                        0                          1
0                        0                          1
0                        0                          1
Read more