使用MS Access如何执行具有多个联接和where子句的更新? [英] Using MS Access how to perform an update with multiple joins and where clauses?

问题描述

在使某些MS Access SQL正常工作时，我遇到了一些麻烦.这是高层次的:

I am having a bit of trouble with getting some MS Access SQL to work. Here is the high level:

我在一个表by15official中有值，我需要使用这些值来更新另一表investmentInfo中的相关记录.非常简单，除了需要执行很多连接以确保正确记录在investmentTable中的更新外，我想我可以使用常规sql来解决这个问题，但是Access的运行效果不佳.以下是我尝试使用的SQL(导致此错误:查询表达式中的语法错误(缺少运算符)..."

I have values in one table, by15official that I need to use to update related records in another table, investmentInfo. Pretty straight forward except there are quite a few joins I need to perform to make sure the right record is updated in the investmentTable and I think I could figure this out with regular sql, but Access is not playing nicely. The following is my sql I am trying to use (which results in this error: "Syntax error (missing operator) in query expression ..."

update ii
set ii.investmentType = by15.InvestmentType
from investmentInfo as ii
inner join 
    (select by15Official.InvestmentType, by15Official.InvestmentNumber
     from (((by15official left join investmentinfo on by15official.InvestmentNumber = investmentInfo.investID) 
    left join fundingSources on fundingSources.investId = investmentInfo.id)
    left join budgetInfo on budgetInfo.fundingID = fundingSources.id)
    where investmentinfo.submissionType = 2
    and budgetInfo.byYear = 2015
    and budgetInfo.type = 'X') as by15
on by15.InvestmentNumber = ii.investID

这似乎应该工作，我试图加入提供investmentType的这组表，这是我要在主表investmentInfo中更新的表.有什么想法吗?可以在Access中完成吗?我四处搜寻，发现上面的内容适合我的需要(实际上，我很确定自己在SO上找到了上面的内容).

This seems like it should work, I am trying to join this group of tables that provide the investmentType which is what I want to update in the main table investmentInfo. Thoughts? Can this be done in Access? I have googled around and found the above which I adapted to meet my needs (actually I am pretty sure I found the above on SO).

有想法吗?

非常感谢！

推荐答案

我确实在MS论坛上得到了某人的帮助.解决的办法是格式化我的SQL稍有不同.这是最终起作用的代码.

I did get some help from someone over at the MS forums. The solution was to format my SQL slightly differently. Here is the code that eventually worked.

UPDATE 
(
 (
   by15official LEFT JOIN investmentinfo 
   ON by15official.InvestmentNumber = investmentInfo.investID 
 )
 LEFT JOIN 
 fundingSources 
 ON investmentInfo.id = fundingSources.investId 
) 
LEFT JOIN budgetInfo 
ON fundingSources.id = budgetInfo.fundingID

SET investmentInfo.investmentType = by15official.InvestmentNumber

WHERE (investmentinfo.submissionType = 2)
And (budgetInfo.byYear = 2015)  

也许上述Access SQL可以帮助其他人.

Perhaps the above Access SQL can help others.

基本上，您想在执行set和where子句之前进行更新和联接.是有道理的，而且我敢肯定，如果我会更熟练地编写SQL，我会知道的.

Basically you want to do the update and the joins before doing the set and where clauses. Makes sense, and I am sure if I were better skilled at writing SQL I would have known that.

这篇关于使用MS Access如何执行具有多个联接和where子句的更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！