What is wrong with my SQL syntax for an UPDATE with a JOIN?

Question

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.Value='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?

Answer 1

One solution is to write in Hoyle ANSI (which does not recognize the FROM clause in an UPDATE statement):

UPDATE TableB
SET BValue="Barmy"
Where Name="Beta"
    And Exists( Select 1 
                From TableA As TA 
                Where TA.Name = "Alpha"
                    And TA.AID = TableB.AID );

One last addition, Access deals with JOINS in an UPDATE statement by putting them in the UPDATE clause like so:

UPDATE TableB
    INNER JOIN TableA tA
        ON TableB.AID=tA.AID    
SET TableB.BValue="Barmy"
WHERE TableB.Name="Beta"
  AND tA.Name="Alpha";

EDIT One other item, Access uses double quotes to identify strings, not single quotes.

Answer 2

it may not know what tB is until you get to the aliasing in the FROM line. Try UPDATE TableB ....

Answer 3

I have to be honest i haven't touched ACCESS in years (luckily) this thread seems to suggest this is the correct syntax.

UPDATE TableB tB
SET tB.BValue='Barmy'
INNER JOIN TableA tA
  ON tB.AID=tA.AID
WHERE tB.Name='Beta'
  AND tA.Name='Alpha';

Answer 4

I reckon, based on a test:

UPDATE TableB tB
INNER JOIN TableA tA
  ON tB.AID=tA.AID
SET tB.Value='Barmy'
WHERE tB.Name='Beta'
  AND tA.Name='Alpha';

Answer 5

Oddly, I've discovered the answer is to use an implicit JOIN:

UPDATE `TableA`, `TableB` 
 SET `TableB`.Value = 'Barmy'
WHERE `TableA`.AID=`TableB`.AID
 AND `TableA`.Name='Alpha'
 AND `TableB`.Name='Beta';

Answer 6

Does Access do subqueries?

UPDATE TableB
SET TableB.Value='Barmy'
WHERE TableB.AID = (SELECT TableA.AID WHERE TableA.Name='Alpha')