18

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
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user1534664
  • 3,258
  • 8
  • 40
  • 66
  • 1
    If I'm not mistaken you need a relationship between `EXAMPLETABLE` and the thing you select from, than you could use something like `UPDATE EXAMPLETABLE SET EXAMPLETABLE.FermentId = (SELECT FERMENT.FermentId FROM FERMENT INNER JOIN [BELGIUM BEER] ON FERMENT.FermentName = [BELGIUM BEER].FermentId WHERE EXAMPLETABLE.relationFieldName = [BELGIUM BEER].relationField ORDER BY [BELGIUM BEER].BeerId)` – xception Oct 14 '12 at 12:58

3 Answers3

26

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

Arnoud Kooi
  • 1,588
  • 4
  • 17
  • 25
13

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;
MJH
  • 839
  • 1
  • 17
  • 37
Cor Rutkowski
  • 131
  • 1
  • 2
7

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!

Gidil
  • 4,137
  • 2
  • 34
  • 50