SQL, Multiple if statements, then, or else?

Question

I am new to SQl and try to learn on my own.

i am learn the usage of if and else statement in SQL

Here is the data where I am trying to use, if or else statement in the below table, i wish to have the comments updated based on the age using the sql query, let say if the age is between 22 and 25, comments" under graduate"

Age: 26 to 27, comments " post graduate"
Age: 28 to 30, comments "working and single"
Age: 31 to 33, comments " middle level manager and married"

Table name: persons

personid  lastname  firstname   age comments
1         Cardinal  Tom         22
2         prabhu    priya       33
3         bhandari  abhijeet    24
4         Harry     Bob         25
5         krishna   anand       29
6         hari      hara        31
7         ram       hara        27
8         kulkarni  manoj       35
9         joshi     santosh     28

possible duplicate of [How do you perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select) — Adi Inbar, Jan 15 '14 at 22:27

Vignesh Kumar A · Accepted Answer · 2014-01-15T05:57:18.717

6

How To Use Case

Try with CASE Statement

Select personid,lastname,firstname,age,
       Case when age between 26 and 27 then 'post graduate'
            when age between 28 and 30 then 'working and single' 
            when age between 31 and 33 then ' middle level manager and married' 
            Else 'Nil'
       End comments 
from persons

edited Jan 15 '14 at 05:57

answered Jan 15 '14 at 05:42

Vignesh Kumar A

27,863
13
63
115

what error you getting in this? – Vignesh Kumar A Jan 15 '14 at 05:51
vignesh, can you recommend a book through which i can build my sql knowledge and I wish to get good hands on both basic as well as advance of SQL. Just to tell, That I don't have any technical background. – user3069018 Jan 15 '14 at 05:52
Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'Case'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'comments'. – user3069018 Jan 15 '14 at 05:52
http://technet.microsoft.com/en-us/sqlserver/default go through this site – Vignesh Kumar A Jan 15 '14 at 05:58

score 1 · Answer 2 · answered Jan 15 '14 at 05:41

I would probably go with a CASE statement

Something like

SELECT  *,
        CASE
            WHEN age BETWEEN 22 AND 25 
                THEN 'under graduate'
            WHEN age BETWEEN 26 AND 27
                THEN 'post graduate'
            WHEN age BETWEEN 28 AND 30 
                THEN 'working and single'
            WHEN age BETWEEN 31 AND 33
                THEN 'middle level manager and married'
            ELSE 'TADA'
        END Comment
FROM    persons

This works but it is creating another column for comments. can we not use else statement login in this. — user3069018, Jan 15 '14 at 05:43

score 1 · Answer 3 · answered Jan 15 '14 at 07:12

As by your requirement, we can't use if...else statement. Case..when statement will be most suitable one. And another one thing We can't able to use if...else inside of any queries(I mean inside of select, insert, update).

And your

Select personid,lastname,firstname,age,
   Case when age between 26 and 27 then 'post graduate'
   Case when age between 28 and 30 then 'working and single' 
   Case when age between 31 and 33 then ' middle level manager and married' 
   Else 'Nil'
   End comments 
from persons

score 0 · Answer 4 · answered Jan 15 '14 at 05:43

0

In standard SQL, you'd use a CASE statement.

update persons
set comments = case when age between 22 and 25 then 'undergraduate'
                    when age between 26 and 27 then 'post graduate'
               -- etc.
               end;

answered Jan 15 '14 at 05:43

Mike Sherrill 'Cat Recall'

91,602
17
122
185

SQL, Multiple if statements, then, or else?

4 Answers4

How To Use Case