creating a script that will update mutliple records in one column

[Rjconrep]

I am trying to update the state field in my database.
I want the state names to be abbreviated.
I have written this query:

UPDATE table2 SET state = "VA"
WHERE (state="virginia"

but it will only update one variable. I need all of the states to be abbreviated.
HELP!

 

[tlbroadbent]

Do you mean you want to update "New York" to "NY", "Florida" to "FL", and so forth? I suppose you want to do it all in one query. I suggest that you create a conversion table.

State Abbr
Alabama AL
Alaska AK
.
.
.
Wyoming WY

Then write an UPDATE query.

Update table2 As t Inner Join ConvTable As c
On t.State = c.State
Set t.State = c.Abbr

------------------------

This should be much easier than multiple queries or a massive query using a function or functions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.