SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,018
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    MS Access update multiple rows with one query question

    I'm not sure that I can do this in SQL but if I can it might be more efficient than looping in code. I have a two tables that are related via a third table.

    The first table is for members and just has a bunch of personal info: name, address, email, etc... An ID column is the key.

    The second table is for professional services each individual offers and also has an ID column which is the key

    The third table has two columns... one for member IDs and the other for services IDs. Both columns are keys.

    I have an editing form (webpage) that lists out professional services with a checkbox for each. I know I can loop through the results of an update form in ASP VBScript or ASP.NET and update each one that has been selected but I wonder if there isn't a more efficient and cleaner method of doing it with SQL.

    I would need to:

    • insert a record if it doesn't exist and has been submitted from the form
    • remove a record if it does exist but hasn't been submitted

    And that's about it. Not difficult to do with code but I think a little primitive.

    Any ideas?

    Thanks!
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    since the 3rd table consists only of two id columns, the approach is really simple -- for the given professional, just delete all his rows and then add everything that was checked on the form

    that way you don't need to SELECT first to see what he's already got, and don't need to compare it to whatever was submitted
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    ^^^What he said. You can run the entire thing inside a transaction to make sure you don't wipe out relationships, have an error, and never recreate them.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    I would agree with both comments above if, and only if the form is prepopulated with the services under contract already. I know I personally wouldn't even glance at that section if I wasn't planning on adding/removing a service. So if it's not prepopulated, I am sure I wouldn't be the only one losing services without my specifically choosing to lose them.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,018
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys,
    Rudy, that is way too logical and efficient... I think I'll have to run some loops in code, compare each item and then DELETE or UPDATE on a per item basis

    I can't believe I didn't think about that while I was writing out the explanation of what I intended. Thanks!

    wwb_99. Thanks, I will be using transactions so I can roll back if something goes awry.

    DaveMaxwell. Yes, the page pre-populates the form with all of the currently selected professional services from the table. The objective is to provide a simple interface for the individuals to review and update their profiles as well as a master interface for the admin to update any individual's profile.

    Thanks again. I'm still surprised I didn't think of such a simple solution.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


Bookmarks

Posting Permissions

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