从内部联接更新 SQL [英] SQL Updating from an inner join

问题描述

我使用的是 Microsoft Access.

I'm using microsoft Access.

如果这个查询:

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

返回 FermentId，如何使用该列更新不同的表?

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

示例:

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

推荐答案

假设子查询返回多个值，则需要第二个键来连接(JOIN)新值和要更新的表(示例).

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)

注意，在这种情况下你需要保证子查询永远不会返回多于一行！

这篇关于从内部联接更新 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！