Updating multiple fields in one table via Update query

[Jeffro]

Hi,
Have a table designed as follows

equip1 text
equip2 text
equip3 text
equip4 text
equip5 text
equip6 text
equip7 text
equip8 text
equip9 text
equip10 text


What I need to do is to check each field and if the field is =
"Tennessee" then
update it to read "TN"


Is there a way to do this in one update query vs having to write 10
different update queries?


Thanks

 

[Douglas J. Steele]

You've just discovered yet another problem with not properly normalizing
tables. Having fields like that is referred to as a Repeating Group, and
it's definitely not recommended. You might check some of the resources Jeff
Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

You could try:

UPDATE MyTable
SET equip1 = Replace([equip1], "Tennessee", "TN")
, equip2 = Replace([equip2], "Tennessee", "TN")
, equip3 = Replace([equip3], "Tennessee", "TN")
....
, equip10 = Replace([equip10], "Tennessee", "TN")

but I would expect that to be fairly slow (and contribute to database
bloat), since it will update all 10 fields on every row, even if it doesn't
change anything.

You could minimize the impact a little, I suppose, by using:

UPDATE MyTable
SET equip1 = Replace([equip1], "Tennessee", "TN")
, equip2 = Replace([equip2], "Tennessee", "TN")
, equip3 = Replace([equip3], "Tennessee", "TN")
....
, equip10 = Replace([equip10], "Tennessee", "TN")
WHERE equip1 = "Tennessee'
OR equip2 = "Tennessee'
OR equip3 = "Tennessee'
....
OR equip10 = "Tennessee'

but it's still going to be a dog.

 

[Jeffro]

Thanks for the reply. I inherited administration of this database from
a ex-employee who created this database and then retired recently.

 

[John W. Vinson]

What I need to do is to check each field and if the field is =
"Tennessee" then
update it to read "TN"


Is there a way to do this in one update query vs having to write 10
different update queries?

Will you also need to update "Kentucky" to "KY", "Ohio" to "OH"...?

I think it is time (or past time!) to normalize your table structure! It will
be no more work than doing this update, and you'll have a much easier time in
the future.


John W. Vinson [MVP]