T-SQL Syntax Issue Else if style logic

Question

two questions today, I'm a busy bee and luckily I have an awesome community at my disposal!

My issue here is this:

I have a field i need to update based on existing field data.

If Gender = F then foo = 1

If Gender = M then foo = 2

If Gender = Male then foo = 2

If Gender = Female then foo = 1

If Gender is not above then foo = 3

Here is what I have:

update EmailAddresses 
set Priority1 = '1'
where GENDER__C = 'Female'

update EmailAddresses 
set Priority1 = '2'
where GENDER__C = 'Male'

update EmailAddresses 
set Priority1 = '1'
where GENDER__C = 'F'

update EmailAddresses 
set Priority1 = '2'
where GENDER__C = 'M'

update EmailAddresses 
set Priority1 = '3'
where GENDER__C not in (select 'Female', 'Male', 'F', 'M')

Any help much appreciated! And its Friday!! Whoo hoo

Nevermind. Answers came in whilst I was crafting this comment. — Daniel Pratt
– Daniel Pratt, Commented Apr 23, 2010 at 14:58

MartW · Accepted Answer · 2010-04-23 14:57:32Z

4

Change it to a CASE statement :

UPDATE EmailAddresses
SET Priority1 = Case
    When GENDER_C IN ('Female', 'F') Then '1'
    When GENDER_C IN ('Male', 'M') Then '2'
    Else '3'
End
FROM EmailAddresses

answered Apr 23, 2010 at 14:57

MartW

12.6k3 gold badges46 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Robinson · Accepted Answer · 2010-04-23 14:56:39Z

3

update EmailAddresses set
    Priority1 = case GENDER__C
        when 'Female' then 1
        when 'F' then 1        
        when 'Male' then 2
        when 'M' then 2
        else 3 end

answered Apr 23, 2010 at 14:56

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

3 Comments

Matt Over a year ago

You diamond, remembered for next time! Have a good weekend mate.

Adam Robinson Over a year ago

If you found the answer helpful, an upvote and accepting the answer would be appreciated ;)

Matt Over a year ago

Of course, standard procedure, the ten min wait is slightly annoying on the accept :)

Collectives™ on Stack Overflow

T-SQL Syntax Issue Else if style logic

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related