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;

Answer 3

Assuming that the sub-query returns more than one value, you need a second key to connect (JOIN) between the new values and the table you want to update (EXAMPLETABLE).

In this case I would try something like this:

UPDATE T 
SET    T.FERMENTID = A.FERMENTID 
FROM   EXAMPLETABLE AS T 
       INNER JOIN (SELECT FERMENT.FERMENTID, 
                          FERMENT.OTHERID 
                   FROM   FERMENT 
                          INNER JOIN [BELGIUM BEER] 
                                  ON FERMENT.FERMENTNAME = 
                                     [BELGIUM BEER].FERMENTID 
                   ORDER  BY [BELGIUM BEER].BEERID) AS A 
               ON A.OTHERID = T.OTHERID 

If this isn't the case and the sub-query returns a single value, try something like this:

UPDATE EXAMPLETABLE 
SET    T.FERMENTID = (SELECT FERMENT.FERMENTID
                      FROM   FERMENT 
                             INNER JOIN [BELGIUM BEER] 
                                     ON FERMENT.FERMENTNAME = 
                                        [BELGIUM BEER].FERMENTID 
                      ORDER  BY [BELGIUM BEER].BEERID)

Note that in this case you need to guarantee that the sub-query will never return more than one row!