CODEWITHSUNDEEP
sqlsql-server
optimisteve
optimisteve

Reputation: 101

UPDATING records in a column based on a condition of another column

Same way IF statement works in Excel, I have a table in SQL with blank fields under gender column. I would like the column to be populated with Male or Female based on the title column...ie UPDATING TABLE so that if Mr, add Male under gender record, if Miss or Mrs, add Female to gender column Table name is Kimobase

     Title  Firstname    Lastname    Telephone  Title  Gender   More columns 
1    Mr     Adam             Smith         001    Mr
2    Mrs    Angela           Evans         002    Mrs
3    Mr     Bill             Towny         003    Mr
4    Miss   Dame             Beaut         004    Miss

I am interested in transforming it as per below

     Title  Firstname    Lastname    Telephone  Title  Gender   More columns 
1    Mr     Adam             Smith         001    Mr    M
2    Mrs    Angela           Evans         002    Mrs   F
3    Mr     Bill             Towny         003    Mr    M
4    Miss   Dame             Beaut         004    Miss  F

Many thanks

Upvotes: 0

Views: 92

Answers (3)

Sagar Shelke
Sagar Shelke

Reputation: 517

Try this:

UPDATE Kimobase
SET Gender = CASE 
             WHEN Title ='Mr' THEN 'M'
             WHEN Title IN ('Mrs','Miss') THEN 'F'
             ELSE ''
          END 
WHERE ISNULL(Gender,'')=''

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this with a case statement:

update Kimobase
    set gender = (case when title in ('Mr') then 'M'
                       when title in ('Mrs', 'Miss', 'Ms') then 'F'
                  end)
    where gender is null;

Upvotes: 2

Matt
Matt

Reputation: 14341

UPDATE table
  SET Gender = CASE
                WHEN Title IN ('Mrs','Miss','Ms') THEN 'F'
                WHEN Title = 'Mr' THEN 'M'
                ELSE ''
               END
WHERE LEN(COALESCE(Gender,'')) = 0

Upvotes: 1

Related Questions