-
Dec 4th, 2022, 12:12 PM
#1
[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
-
Dec 4th, 2022, 12:18 PM
#2
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
-
Dec 4th, 2022, 12:39 PM
#3
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
-
Dec 4th, 2022, 12:59 PM
#4
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
-
Dec 4th, 2022, 11:08 PM
#5
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
-
Dec 5th, 2022, 03:25 AM
#6
Re: Access Update With Join
Originally Posted by techgnome
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
-
Dec 5th, 2022, 03:45 AM
#7
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>
Last edited by wqweto; Dec 5th, 2022 at 03:52 AM.
-
Dec 5th, 2022, 07:59 AM
#8
Re: Access Update With Join
Originally Posted by Zvoni
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
-
Dec 5th, 2022, 03:33 PM
#9
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
-
Dec 6th, 2022, 06:35 AM
#10
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>
-
Dec 6th, 2022, 04:16 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|