MS Access：有多个连接的更新语句

问题描述：

我试图根据其他两个表（TDCE_NE和TDCE_NE_COMP）更新表（DMS_TEST_LOAD）。任何想法下面的查询有什么问题？我不断收到无用的“UPDATE语句中的语法错误”。来自Access的错误。提前致谢。MS Access：有多个连接的更新语句

UPDATE DMS_TEST_LOAD AS DMS 
((INNER JOIN TDCE_NE_COMP AS COMP 
ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] AND COMP.[NE_COMP_TYPE_ID]=421) 
INNER JOIN TDCE_NE AS NE ON NE.[ID]=COMP.[NE_ID] AND NE.[NAME]=DMS.[Office]) 
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1];

你不需要从内部连接？ – boomoto 2014-10-10 17:02:34

为什么不在Access的“设计视图”中构建它？它会为你写出声明。 – 2014-10-10 17:11:18

@iDevlop因为Access将以最简单，最低效的方式开发查询，并且我的查询将永远运行。 – user1947544 2014-10-10 17:49:32

答

不太确定SQL系统MS访问使用什么。但你可能想试试这个

UPDATE DMS 
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1] 
FROM DMS_TEST_LOAD AS DMS 
INNER JOIN TDCE_NE_COMP AS COMP 
    ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] 
     AND COMP.[NE_COMP_TYPE_ID]=421 
    INNER JOIN TDCE_NE AS NE 
    ON NE.[ID]=COMP.[NE_ID] AND NE.[NAME]=DMS.[Office];

没有运气，@Alex。不幸的是，MS Access没有这种直观的语法。我相信在加入DMS之前必须加入联盟[Char Parm 1（Trk Dir）]。 – user1947544 2014-10-10 17:07:21

在此处找到示例：http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html#UPDATE_Query_SQL_Syntax UPDATE tblAddress INNER JOIN tblZipCodes ON [tblAddress]。[ZipCode] = [tblZipCodes]。[邮政编码] SET [tblAddress]。[City] = [tblZipCodes]。[City]， [tblAddress]。[State] = [tblZipCodes]。[State] WHERE（[Country] ='US'） – Alex 2014-10-10 17:13:55

could the问题与你喜欢？你需要将字符串与另一个字符串进行比较吗？ http://stackoverflow.com/questions/14236410/access-query-where-one-field-is-like-another – Alex 2014-10-10 17:20:35

答

在MS Access中，你必须确保在连接中正确使用括号。我相信你是以下后：

UPDATE DMS 
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.CHAR_PARM1 
FROM (DMS_TEST_LOAD AS DMS 
    INNER JOIN TDCE_NE_COMP AS COMP 
    ON (COMP.NAME = DMS.[Trunk Group Number (TGN)]) AND (COMP.NE_COMP_TYPE_ID = 421)) 
    INNER JOIN TDCE_NE AS NE 
    ON (NE.Name = DMS.Office) AND (COMP.NE_ID = NE.ID)

注意我删除了LIKE与=取而代之。您没有使用任何通配符，因此它甚至不能作为LIKE。如果你想使用LIKE你可能会寻找以下：

COMP.NAME LIKE "*" & DMS.[Trunk Group Number (TGN)] & "*"

答

如何这样的事情...

UPDATE DMS 
SET [Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1] 
FROM DMS_TEST_LOAD DMS 
INNER JOIN TDCE_NE_COMP AS COMP 
    ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] 
    AND COMP.[NE_COMP_TYPE_ID]=421 
INNER JOIN TDCE_NE AS NE 
    ON NE.[ID]=COMP.[NE_ID] 
    AND NE.[NAME]=DMS.[Office]

未经测试，但重构为ANSI92“标准” SQL

并可以测试（无需实际更新ofc）...

SELECT [Char Parm 1 (Trk Dir)], COMP.[CHAR_PARM1] 
FROM DMS_TEST_LOAD DMS 
INNER JOIN TDCE_NE_COMP AS COMP 
    ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] 
    AND COMP.[NE_COMP_TYPE_ID]=421 
INNER JOIN TDCE_NE AS NE 
    ON NE.[ID]=COMP.[NE_ID] 
    AND NE.[NAME]=DMS.[Office]

MS Access：有多个连接的更新语句

相关推荐