Multiple Update values in one query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I use multiple values in one update query to update one field based on a
value in the field. I have a field that can have 5 possible codes (0,1,2,3,8)
and I would like to run one update query (I know I can write 5 different, but
hope can do in one) to change these values to their actual device
equivalants, for easier to read. If value = 0 then Pollcat II, value 1
Pollcat III, value 2 Netlink, value 3 CC3, value 8 FTP. Is there a way to
do a Case type statement in an update query?

Thanks in advance

Kenny A.
 
Can I use multiple values in one update query to update one field based on a
value in the field. I have a field that can have 5 possible codes (0,1,2,3,8)
and I would like to run one update query (I know I can write 5 different, but
hope can do in one) to change these values to their actual device
equivalants, for easier to read. If value = 0 then Pollcat II, value 1
Pollcat III, value 2 Netlink, value 3 CC3, value 8 FTP. Is there a way to
do a Case type statement in an update query?

Thanks in advance

Kenny A.
Click to expand...

One way would be creating a translator table with columns DEVICE_ID
and DESCRIP for example. Then storing "0", "Pollcat II" and so on.
Then joining translator table to updated table in your update query
using column DEVICE_ID.

Although a "case statement" would work for a 5-values scenario, think
about what would happen if instead of 5 possible values there could be
5000 of them. All you would have to do is insert the 5000 records into
the translator table.
 
Chris,
Although your way will work, is there a way within the query itself to do
a case or if statements (maybe IIF statements)? There are only the 5 types I
described, if anything, maybe one or two more could be added max. I know I
could do 5 easy update queries within a macro (one for each value), but tryig
to do within one query. Is it possible to do via case or IIF statements (or
other conditional form)?

Thanks
Kenny A.
 
Chris,
Although your way will work, is there a way within the query itself to do
a case or if statements (maybe IIF statements)? There are only the 5 types I
described, if anything, maybe one or two more could be added max. I know I
could do 5 easy update queries within a macro (one for each value), but tryig
to do within one query. Is it possible to do via case or IIF statements (or
other conditional form)?

Thanks
Kenny A.







- Show quoted text -
Click to expand...

Nested IIF statements should do it.

iif (value=0, "Pollcat II", iif (value=1, "Pollcat III", iif
( ...etc... )))
 
Back
Top