Updating Multiple Records Based on Field Value

[TexasStar]

Hello, I have searched for help on this but it just has me stumped. I
tried doing several different things, but none of them worked.

Here is the Scenario:

I have a table called Table1 and that table has several columns. Two
of the columns are titled 'State' and the other is titled 'API'.

The State fields are blank, and the API fields contain a number in
this format for example: 42-321-4567

What I am trying to do is update each State field based on the first 2
digits of the API number. I want to read each API, determine what 2
digit state code is represents and then update the State field with
the correct name for that state.

Can someone point me in the right direction and give me some help in
accomplishing this? Any help or advice would be greatly appreciated. I
just can't crack this and I feel like it should be something pretty
simple. I was thinking about setting up cases depending on what 2
digit code is trimmed and then run a special SQL update to set the
State field correctly, but I haven't been able to get it working. If
anyone could code me up an example that I could expand on it would be
greatly appreciated.

 

[Guest]

ASSUMPTION: your API codes are one-to-one with the states, i.e., 43 is
always Massachusetts and no other code is also Massachusetts.

Yes, you could use a really long SELECT CASE statement, with your case being
Left(API,2). Alternatively, you could set up a reference table containing
all the states and the equivalent first-two-ofAPI, then use that table as the
basis for an update query.