CODEWITHSUNDEEP
sql-servert-sql
Matt
Matt

Reputation: 4117

T-SQL Syntax Issue Else if style logic

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

Upvotes: 0

Views: 300

Answers (2)

MartW
MartW

Reputation: 12538

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

Upvotes: 4

Adam Robinson
Adam Robinson

Reputation: 185623

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

Upvotes: 3

Related Questions