Syntax Error SQL UPDATE in MS Access with JOIN on same table

Question

I'm getting a syntax error within MS Access VBA whereas the same line perfectly works fine in SQL Server:

UPDATE a 
SET a.Result = b.Result, a.Part = b.Part 
FROM tbl_Parts a 
JOIN ( SELECT b.* FROM tbl_Parts b WHERE b.ID = 180) b 
ON b.Part_Nr = a.Part_Nr;

I'm trying to use one row as a template and copy it's content to already existing other rows from the same Part-ID.

In Msacess the SET comes after the JOIN. The syntax is UPDATE TBL1 INNER JOIN TBL2 ON TBL1.X = TBL2.Y SET TBL1.P = TBL2.Q — Jonathan Willcock
– Jonathan Willcock, Commented Jul 31, 2022 at 15:31
@JonathanWillcock: Is there any documentation about the differences? — karlo922
– karlo922, Commented Aug 1, 2022 at 6:18
Not that I know of - for my own purposes I have a list of standard syntax for UPDATE/DELETE on joins for the DBs that I most familiar with, but I don't know of any site that gives a complete comparison. — Jonathan Willcock
– Jonathan Willcock, Commented Aug 1, 2022 at 10:48

June7 · Accepted Answer · 2022-07-31 16:16:33Z

1

Consider:

UPDATE tbl_Parts AS a 
INNER JOIN (SELECT * FROM tbl_Parts WHERE ID = 180) AS b
ON b.Part_Nr = a.Part_Nr
SET a.Result = b.Result, a.Part = b.Part;

answered Jul 31, 2022 at 16:16

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax Error SQL UPDATE in MS Access with JOIN on same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related