Updating multiple fields in one table via Update query

  • Thread starter Thread starter Jeffro
  • Start date Start date
J

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
 
Jeffro said:
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
Click to expand...

UPDATE TableName
SET equip1 = IIf(equip1 = "Tennessee", "TN", equip1),
equip2 = IIf(equip2 = "Tennessee", "TN", equip2),
equip3 = IIf(equip3 = "Tennessee", "TN", equip3),
equip4 = IIf(equip4 = "Tennessee", "TN", equip4),
etc..

Essentially you are upating all of the fields all of the time, but when they
are not equal to "Tennessee" then you are setting them equal to themselves.
 
Back
Top