Results 1 to 8 of 8

Thread: Update query

  1. #1
    Join Date
    Aug 2008
    Posts
    16

    Unanswered: Update query

    --------------------------------------------------------------------------------

    I have two tables which are linked. To search for records i currently have the query (ignore the extras, this is done through java so the query looks a bit different)

    Code:

    "select r.result, c.Nationality, c.First_Name, c.Last_Name " +
    "FROM (((tblResults as r " +
    "WHERE e.Event_Name = ? " +
    "AND r.Round_ID = ? ";

    So this query returns everything i need from these two tables. Now i want to update both of these tables with a whole new set of data. So would i keep the query simular.

    I am trying somthing like


    UPDATE tblCompetitor, tblResults
    SET r.result, c.Nationality, c.First_Name, c.Last_Name
    inner join tblCompetitor as c on r.Competitor_ID=c.ID)
    inner join tblEvent e on r.Event_ID=e.ID)
    inner join tblRound ro on r.Round_ID=ro.ID)
    WHERE e.Event_Name = ?
    AND r.Round_ID = ?

    Is this above looking how it should do with an update statement? Or how would i do somthing like this. If you need any more information, i can post the tables so you can see the relationships.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You cannot update two tables at once.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    You cannot update two tables at once.
    Depends on the RDBMS. JET can
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your SQL is nowhere near right. Can you create this query using the query builder to get the correct syntax and then copy the SQL into your JAVA app?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Depends on the RDBMS. JET can

    Really?
    But multi-table update statements just don't make sense in my pretty little head, gotsan example?
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2008
    Posts
    16
    Let me try and explain this a bit better. So for the sake of this problem, i have two tables. One called tblCompetitor and one called tblResults. Table names should symbolise what they are.
    tblCompetitor
    ID
    Nationality
    First_Name
    Last_Name

    tblResults
    Competitor_ID
    Result

    The tables are linked through ID-Competitor_ID.

    Now in my display (This is a java display), the user has a table in which they can enter data. The table columns are
    Nationality, First_Name, Last_Name and Time_Set
    The first three relate the tblCompetitor and the last one relates to tblResults.

    So when i search for these four variables, i have produced a query as so (please egnore the syntax, its java not sql)
    Code:
    	   "select r.result, c.Nationality, c.First_Name, c.Last_Name " +
    	   "FROM (((tblResults as r " +
          		"inner join tblCompetitor as c on r.Competitor_ID=c.ID) " +
                "inner join tblEvent e on r.Event_ID=e.ID) " +
                "inner join tblRound ro on r.Round_ID=ro.ID) " +
                "WHERE e.Event_Name = ? " +
                "AND r.Round_ID = ? ";
    But now i need to update the records, so if the user edits a Nationality column and a results column, this would mean that the update needs to be done across two tables wouldnt it?

    What would the query look like if i wanted to update everything and replace it with all new stuff?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - I understood completely.

    My point will save you lots of time - build your SQL in Access first. Test it, check it works. Then move it to JAVA. Test it, check it works. Then add the parameters. Test it, check it works. Build up your code slowly, iteratively. If you can't write JET SQL, don't try writing it in a JAVA string. Use the query designer and use the SQL it generates (i.e. get Access to do the hard work). Working straight from JAVA using SQL you clearly haven't grasped fully (your update statement is nowhere near) just makes things harder.

    I don't have time to check this in Access however, IIRC, you can get from a SELECT statement to an UPDATE in Access by changing FROM to UPDATE, SELECT to SET and then move the whole clause to the bottom.

    Something like:
    Code:
    UPDATE (((tblResults as r 
    inner join tblCompetitor as c on r.Competitor_ID=c.ID) 
                inner join tblEvent e on r.Event_ID=e.ID) 
                inner join tblRound ro on r.Round_ID=ro.ID) 
    SET r.something = "This", c.somethingelse = "That"
    The WHERE clause would come after the SET "clause". Test in Access on data you can lose. Once figured out, move to JAVA.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Test in Access only after you've backed up!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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