CODEWITHSUNDEEP
sql-server
user4593252
user4593252

Reputation: 3506

SQL: change value in field to equivalent

Pardon me if my question is utterly simple but SQL isn't really my strongest suit and I'm failing at google and SO isn't suggesting anything useful here...

If I had a table in SQL (SQL Server) that had a state column where yahoos were putting in anything they wanted and it was my job to clean it up and standardize the inputs (ignoring people who put numbers, countries, etc in the state column), how would I go about checking for those entries that actually fit a normal profile so that it could be converted to the two letter state abbreviation?

Say for example:

AddressID   UserID  StateProvince   CountryCode
536         17      Oregon          US
537         18      Washington      US

And I want to convert those states to their appropriate state abbreviations....

There is currently another table which lists all the administrative divisions and their abbreviations like so:

ID      StateProvinceCode   CountryCode Name
1       AL                  US          Alabama
2       AK                  US          Alaska

I imagine something similar to (psuedo)

UPDATE Address
    SET StateProvinceCode=
    Case StateProvinceCode
        WHEN 'Alabama' THEN 'AL' 
        WHEN 'Alaska' THEN 'AK'
        ...
    END,
WHERE CountryCode = 'US'

Any advice?

Upvotes: 0

Views: 79

Answers (3)

Alsin
Alsin

Reputation: 1638

UPDATE a
SET a.StateProvince = d.StateProvinceCode
FROM Address a
  join AdministrativeDivisions d
    on a.CountryCode = d.CountryCode
      and a.StateProvince = d.Name
WHERE 
  a.CountryCode = 'US'

If StateProvince in Address doesn't match with AdministrativeDivisions.Name then value won't be updated, so you'll change only 'normal profile'.

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

You can use following update query to do this simple task : 

UPDATE ADDRESS 
SET StateProvince = B.StateProvinceCode
FROM ADDRESS AS A INNER JOIN STATES AS B ON A.StateProvince = B.NAME

No need to write any case statements for this using this techique.

Upvotes: 2

Beth
Beth

Reputation: 9617

you could join in your divisions table on table.StateProvince = divisions.Name and update StateProvince to StateProvinceCode

UPDATE table
SET StateProvince = StateProvinceCode
FROM table INNER JOIN 
divisions ON 
StateProvince = NAME
WHERE 
CountryCode = 'US'

Upvotes: 0

Related Questions