What is wrong with my SQL syntax for an UPDATE with a JOIN?
Posted
by Phil H
on Stack Overflow
See other posts from Stack Overflow
or by Phil H
Published on 2010-03-19T16:36:56Z
Indexed on
2010/03/19
16:41 UTC
Read the original article
Hit count: 188
I have two tables, related by a common key. So TableA has key AID and value Name and TableB has keys AID, BID and values Name, Value:
AID Name
74 Alpha
AID BID Name Value
74 4 Beta Brilliance
I would like to update the TableB Value here from Brilliance to Barmy, using just the Name fields. I thought I could do it via an UPDATE containing a JOIN, but Access (I know...) is complaining with 'Syntax error (missing operator) in query expression ' and then everything from 'Barmy' here:
UPDATE tB
SET tB.BValue='Barmy'
FROM TableB tB
INNER JOIN TableA tA
ON tB.AID=tA.AID
WHERE tB.Name='Beta'
AND tA.Name='Alpha';
What is my heinous crime? Or is it just Access not conforming?
© Stack Overflow or respective owner