SQL Updating from an inner join

Question

I'm using microsoft Access.

If this query:

(SELECT FERMENT.FermentId
FROM FERMENT
INNER JOIN [BELGIUM BEER]
ON
FERMENT.FermentName = [BELGIUM BEER].FermentId ORDER BY [BELGIUM BEER].BeerId) a

returns FermentId, how do I update a different table with that column?

Example:

UPDATE EXAMPLETABLE
SET EXAMPLETABLE.FermentId = a.FermentId
FROM a
(SELECT FERMENT.FermentId
FROM FERMENT
INNER JOIN [BELGIUM BEER]
ON
FERMENT.FermentName = [BELGIUM BEER].FermentId ORDER BY [BELGIUM BEER].BeerId) a

Answer 1

Not sure what the relation of EXAMPLETABLE with your data is, but in general.

In Access the SET part is after the join, also skip the select part en the order by. Should be something like this

UPDATE FERMENT
INNER JOIN ([BELGIUM BEER]  ON FERMENT.FermentName = [BELGIUM BEER].FermentId) 
SET EXAMPLETABLE.FermentColumn = a.FermentColumn

If it doent work try building the join in the query builder

Answer 2

There is no need for defining a relationship between the two tables. Answer 10 (Arnoldiusss) is almost correct and by far the most comprehensible and shortest solution. And the fastest in execution. But the example code is wrong. The next code comes from one of my applications and runs fine in MS ACCESS 2013.

    UPDATE table1 T1 
    INNER JOIN table2 T2 
    ON T2.Id = T1.Id
    SET T1.myField = T2.myField;

For "the Belgian Beer Case" (I love that expression;-) it would be:

    UPDATE FERMENT AS T1
    INNER JOIN [BELGIUM BEER] AS T2 ON T1.FermentName = T2.FermentId 
    SET T1.FermentColumn1 = T2.FermentColumn1;