Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Multiple Updates, Same Query

    Is it possible to construct an update query that updates multiple records at the same time based on different information? For example, let's say I have a list of 1000 records and one of the fields has the numbers 1,2,3,4 or 5. I want to change those values to something else ALL at the same time. I want all the records with a 1 to become one value, 2 with another value and so on. Right now I have several queries that I run in a row in order to accomplish this. Thanks!
    BillS

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update daTable
       set fld= iif(fld=1,9
               ,iif(fld=2,37
               ,iif(fld=3,187
               ,iif(fld=4,357,937))))
     where fld in (1,2,3,4,5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks, but I'm not sure I understand exactly what is happening. What do the numbers signify after the commas? Basically I'm importing coded data. 1 needs to become RI, 2 needs to become OR, etc.
    BillS

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BillSinc
    What do the numbers signify after the commas?
    they signify the values to translate the numbers 1 through 5 to

    you said "one value, another value, ..." and i was just substituting some values

    try this --
    Code:
    update daTable
       set fld= iif(fld='1','RI'
               ,iif(fld='2','OR'
               ,iif(fld='3','foo'
               ,iif(fld='4','bar','qux'))))
     where fld in ('1','2','3','4','5')
    there is also the problem of datatype, if you really do intend to change 1 to 'RI' then this means that the datatype is TEXT, correct? so i have put quotes around all those numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Yes, it is a TEXT field and that worked great. Thanks!
    BillS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •