这个MS-Access的更新语句有什么问题?

I am trying to update a field based on a field from another table (PricePlan). In PricePlan i have 2 fields, 'Guest' and 'Member' and column 'Price' but in this case i would like 'Price' in my another table to update according to 'Guest's value. When i debug i get the following error in VS2008 'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Operation must use an updateable query.

我正在尝试根据另一个表(PricePlan)中的字段更新一个字段。在PricePlan中，我有两个字段，“Guest”和“Member”以及“Price”，但是在这种情况下，我希望在另一个表中根据“Guest的值”更新“Price”。在调试时，我在VS2008中得到了以下错误:“System.Data.OleDb类型的未处理异常”。发生在System.Data OleDbException”。dll附加信息:操作必须使用可更新查询。

What's wrong with my UPDATE statement?

我的更新声明有什么问题?

UPDATE Bill 
SET Price = ( SELECT PricePlan.Price 
              FROM PricePlan 
              WHERE PricePlan.PricePlanName = 'Guest'
            )
WHERE EXISTS (Select PricePlan.Price 
              FROM PricePlan 
              WHERE PricePlan.PricePlanName ='Guest'
             );

3 个解决方案

#1

As I recall, these update statements go more like:

我记得，这些更新语句更像是:

UPDATE Bill
SET Price = PricePlan.Price
FROM (SELECT Price FROM PricePlan 
      WHERE PricePlanName = 'Guest')

But I'm not entirely clear on what you're trying to accomplish. Can you provide an example of what the tables would contain, in a before-and-after scenario?

但我不太清楚你想要完成什么。在前后场景中，您能否提供一个表将包含哪些内容的示例?

EDIT

编辑

Based on http://dbaspot.com/forums/ms-access/138825-select-inside-update.html, I'm guessing something like this will probably work:

基于http://dbaspot.com/forums/ms-access/138825-select-inside-update.html，我猜这样的东西可能会有用:

UPDATE Bill,PricePlan 
SET Bill.Price = PricePlan.Price
WHERE PricePlan.PricePlanName = 'Guest'
AND ... (put something here to limit which bills you want to update)

#2

There is nothing is 'wrong' per se with you code: it is Intermediate Standard SQL-92 code and nicely formatted too :)

您的代码本身没有什么“问题”:它是中间标准SQL-92代码，格式也很好:)

Sadly, Access (ACE, Jet, whatever) is not SQL-92 compliant, not even Entry Level. The UPDATE syntax is its worst failure, IMO. Circa Access 2000 the SQL Server team took ownership of the engine and tried to make it compliant with Standards but was blocked by the Windows team, who had too much code dependent on Access's proprietary UPDATE syntax. It will now never implement SQL-92, there is no hope.

遗憾的是，访问(ACE、Jet等)不兼容SQL-92，甚至不兼容入门级。更新语法是它最大的失败，IMO。Circa Access 2000 SQL Server团队获得了引擎的所有权，并试图使它符合标准，但被Windows团队阻止，因为Windows团队有太多代码依赖于Access的专有更新语法。它现在永远不会实现SQL-92，没有希望。

It is Access that is 'wrong'. I urge you to move to a more capable SQL product. SQL Server 2008 Express would be my personal choice.

访问是“错误的”。我敦促您转向更有能力的SQL产品。SQL Server 2008 Express将是我的个人选择。

If you cannot/will not move, then you are forced to learn Access's proprietary syntax (not just UPDATE!) along with suffering its many other quirks and limitations.

如果您不能/不愿移动，那么您将*学习Access的专有语法(而不仅仅是更新!)，以及它的许多其他怪癖和限制。

#3

The most glaring error is that there is no correlation between the updated table Bill and the subquery! What are you trying to do?

最明显的错误是更新的表账单和子查询之间没有关联!你想做什么?

Maybe something like the below?

也许像下面这样?

UPDATE Bill inner join PricePlan on PricePlan.Id = Bill.Id
SET Bill.Price = PricePlan.Price
WHERE PricePlan.PricePlanName = 'Guest'

#1