Results 1 to 11 of 11

Thread: [RESOLVED] Access Update With Join

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Resolved [RESOLVED] Access Update With Join

    I have this query (the table names have been altered to something more readily readable):

    Code:
    UPDATE T1 
                                    SET T1.UploadDate = T2.StartDate
                                    FROM T1 INNER JOIN T2 ON T1.SurveyID = T2.SurveyID                    
    WHERE T1.WaypointID IN (SELECT WaypointID FROM T3)
    This doesn't work. I get an error saying that there is a missing operator in this part:
    Code:
     T2.StartDate
                                    FROM T1 INNER JOIN T2 ON T1.SurveyID = T2.SurveyID
    For one thing, that's an odd part to be shown, since it is the right side of the assignment, along with the From clause, but not including the Where clause.

    The SQL is based on some examples that are for SQL Server. So, one possibility is that I'm overlooking something simple, the other possibility is that this is a difference between Access and SQL Server syntax, and a third possibility is 'else'.

    So, which is it? What's wrong with the query?
    My usual boring signature: Nothing

  2. #2

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Access Update With Join

    It appears that I have to alter the order of operations a bit for this to work, as it appears to work if I shift the SET portion after the JOIN:

    UPDATE T1 FROM T1 INNER JOIN T2 ON T1.C = T2.C SET T1.C2 = T2.C2

    That seems kind of odd to me.
    My usual boring signature: Nothing

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Access Update With Join

    Untested
    Code:
    Update T1 
    Inner Join T3 On T1.WaypointID=T3.WaypointID 
    Inner Join T2 On T1.SurveyID=T2.SurveyID 
    Set T1.UploadDate=T2.StartDate
    That seems kind of odd to me.

    It‘ Microsoft. What did you expect? *shrug*
    Last edited by Zvoni; Dec 4th, 2022 at 12:56 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Access Update With Join

    Yeah, that works, as I noted in my own reply. I found a tutorial that I based my original query off of. That tutorial was for SQL Server, though, and when I found for Access, it showed the SET in a different place.

    Along the way, I also found out that my original query was wrong for a totally different reason, but that's unrelated to the question here. I realized that, had it worked, it wouldn't have been ideal, as there was a better update to do based on the problem I was trying to solve. Specifically, that WHERE clause was not a good idea, so T3 was dropped entirely. I thought I only wanted to update a small minority of the rows on T2. In fact, I knew I wanted to update ALL the rows on T2, I just didn't think I knew what to update most of them TO.

    Anyways, the only question I have remaining is: Is this really just a syntax difference between SQL Server and Access, or was I misled by that earlier tutorial?
    My usual boring signature: Nothing

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Access Update With Join

    No, you weren't misled. It's the difference in the dialects between Access and SQL Server. On a similar note, Oracle does it differently as well... and every damned time I have to look it up.
    Sadly the "S" in SQL is "Structured" not "Standard".

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Access Update With Join

    Quote Originally Posted by techgnome View Post
    No, you weren't misled. It's the difference in the dialects between Access and SQL Server. On a similar note, Oracle does it differently as well... and every damned time I have to look it up.
    Sadly the "S" in SQL is "Structured" not "Standard".

    -tg
    Especially considering, since when MS Access is "available" (November 1992), and Microsofts "habit" of doing everything their own way .
    Considering, that MS had to support "Legacy" Sybase, they couldn't move completely the way, Standard/ANSI SQL developed through the time
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Access Update With Join

    Note that MS Access dialect needs more parentheses than MSSQL when doing multi-joins like this

    Code:
    SELECT    *
    FROM      ((MyTable1
    LEFT JOIN MyTable2
    ON        MyTable1.FK1 = MyTable2.ID) -- first pair of parentheses closes here
    LEFT JOIN MyTable3
    ON        MyTable1.FK2 = MyTable3.ID) -- second pair of parentheses closes here
    . . . and a similar real-world sample of SELECT ... INTO query (which is a thing in MS Access too obviously)

    Code:
    SELECT      ....
    INTO        SubmissionCompatibility
    FROM        (((((((((Submission s
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 1) sc1
    ON          s.ID = sc1.SubmissionId AND s.WorldId = sc1.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 2) sc2
    ON          s.ID = sc2.SubmissionId AND s.WorldId = sc2.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 3) sc3
    ON          s.ID = sc3.SubmissionId AND s.WorldId = sc3.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 4) sc4
    ON          s.ID = sc4.SubmissionId AND s.WorldId = sc4.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 5) sc5
    ON          s.ID = sc5.SubmissionId AND s.WorldId = sc5.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 6) sc6
    ON          s.ID = sc6.SubmissionId AND s.WorldId = sc6.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 7) sc7
    ON          s.ID = sc7.SubmissionId AND s.WorldId = sc7.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 8) sc8
    ON          s.ID = sc8.SubmissionId AND s.WorldId = sc8.WorldId)
    LEFT JOIN   (SELECT * FROM TempCompatibility WHERE SeqNo = 9) sc9
    ON          s.ID = sc9.SubmissionId AND s.WorldId = sc9.WorldId)
    . . . and some more parentheses with multi-joins

    Code:
    SELECT      s.ID
                , s.WorldId
                , s.AuthorName
                , s.Title
                , c.Line AS Code
                , s.Description
                , s.Inputs
                , s.Assumes
                , s.CodeReturns
                , s.SideEffects
                , s.ApiDeclarations
                , s.PicturePath
                , s.ZipFilePath
                , s.UserRatingTotal
                , s.NumOfUserRatings
                , sc1.CompatibilityName
                , cat.CategoryId
                , cat.CategoryName
                , dif.Name AS CodeDifficultyName
    FROM        (((((Submission AS s
    INNER JOIN  Code AS c
    ON          s.ID = c.ID AND s.WorldId = c.WorldId)
    LEFT JOIN   Complete AS d
    ON          s.ID = d.ID AND s.WorldId = d.WorldId)
    LEFT JOIN   SubmissionCompatibility sc1
    ON          s.ID = sc1.ID AND s.WorldId = sc1.WorldId)
    LEFT JOIN   Category cat
    ON          s.CategoryId = cat.CategoryId AND s.WorldId = cat.WorldId)
    LEFT JOIN   DifficultyType dif
    ON          s.CodeDifficultyTypeId = dif.DifficultyTypeId)
    WHERE       c.LineNumber = 1 AND d.ID IS NULL
    ORDER BY    s.AuthorName, s.Title, s.ID, s.WorldID
    . . . taken from here where there are more sample queries in the various Fetch procedures incl. an UPDATE with a JOIN syntax.

    cheers,
    </wqw>

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Access Update With Join

    Quote Originally Posted by Zvoni View Post
    Especially considering, since when MS Access is "available" (November 1992), and Microsofts "habit" of doing everything their own way .
    Considering, that MS had to support "Legacy" Sybase, they couldn't move completely the way, Standard/ANSI SQL developed through the time
    Yeah, that was something I've learned the hard way through the years.... The thing about standards is that they tell you what to implement to be compliant... but not how to implement it. What you get as a result is one browser uses margins one way and another does it a different way, with the result being thousands of developers that need to implement all sorts of hacks to get around "the problem".


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Access Update With Join

    The difference that annoys me the most is the lack of named parameters. Naming parameters can still improve readability, you just have to remember that the name doesn't matter in Access.

    I hadn't realized that parentheses were so important in Access. I know that the query wizard uses an absurd number of parentheses, I just thought they were usually optional. I guess I haven't written enough Access queries to have tripped over that one.
    My usual boring signature: Nothing

  10. #10
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [RESOLVED] Access Update With Join

    Btw, parentheses on JOINs are need in MSSQL too. It's natural like you need these in expressions to specify precedence of operations like in (2 + 2) * 2 otherwise if parentheses were always optional they wouldn't exist.

    In MSSQL if you want to OUTER JOIN on two JOIN-ed tables you have to use either a derived table with the two tables JOIN-ed or parentheses. A hypothetical MSSQL query could be something like this:

    Code:
    SELECT      ...
    FROM        MyTable m
    LEFT JOIN   (           ATable a
                JOIN        BTable b
                ON          a.FK1 = b.ID)
    ON          m.FK2 = a.ID
                AND m.SeqNumber = b.SeqNumber
    This is A LEFT JOIN (B JOIN C) join expression with parentheses required like in A - (B + C) math one.

    cheers,
    </wqw>

  11. #11

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Access Update With Join

    Yeah, when they are reasonable, they are reasonable. I sometimes use them when not strictly necessary if I feel that it aids readability, but I've seen Access add an excessive number of parentheses into some queries, even when not necessary. I've always assumed that it was easier to nest things in parentheses for the query designer rather than figure out whether or not they are necessary for the evaluation.

    After all (1+1)+(2+2) needs no parenthesis, but if you are building up that equation from constituent parts, then at the time that the 1+1 is written, you might not know whether the next operator will be +, *, or nothing at all, and wrapping in parentheses is just easier, even if not necessary in all cases.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width