Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116

    Unanswered: Update Query - Multiple Updates For Same Table

    Hi! I am trying to clean up an Access database someone else built, but it's been a few years since I've done any Access work. The previous developer wrote about 12 queries to update 1 table. I would like to consolidate these. There are 3 fields within the table that are used which I'll call Field1, Field2 and Field 3 to simplify.

    QryOne: Update [Table] Set [Table].[Field1] = Mid([Field2],1,2);

    QryTwo: Update [Table] Set [Table].[Field3] = "Param1" Where ((([Table].[Field2])="Param2"));

    QryThree: Update [Table] Set [Table].[Field3] = "Param3" Where ((([Table].[Field2])="Param4"));

    How do I combine these update queries into one?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    UPDATE yourTable
    SET field1 = mid(field2, 1, 2),
    field3 = iif(field2 = "param2", "param1", iif(field2 = "param4", "param3", "whateveTheValueShouldBeIfNeitherConditionIsMe t"))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whateveTheValueShouldBeIfNeitherConditionIsMet = field3

    which means, update it to the value it already is

    which is why i wouldn't bother to combine the three queries, i would just run them separately -- less needless updatin' goin' on that way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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