The State field in our database is a mess. There was no validation when it was filled so we have everything from two letter abbreviations to full state names to misspelled state names to "test" and "xxxx", etc. I am not going to try to handle everything, but for sure I want to fix the correct state names to abbreviations. I have a list of valid state names and abbreviations, but I don't know how I can do this:

UPDATE Table SET State = ('AR','AK') WHERE (SELECT * FROM Table WHERE State IN ('Arkansas','Alaska'))

Basically, can I update a field to be something from a list by the location it is in another list?