操作必须使用一个可更新的查询。 （错误3073）的Microsoft Access [英] Operation must use an updatable query. (Error 3073) Microsoft Access

问题描述

我写这个查询：

UPDATE tbl_stock1 SET 
tbl_stock1.weight1 = (
    select (b.weight1 - c.weight_in_gram) as temp 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    ), 
tbl_stock1.qty1 = (
    select (b.qty1 - c.qty) as temp1 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where 
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    )
WHERE
    tbl_stock1.item_submodel_id = 'ISUBM/1' 
    and tbl_stock1.status <> 'D';

我得到这个错误信息：

I got this error message:

Operation must use an updatable query. (Error 3073) Microsoft Access

但是，如果我运行在SQL Server相同的查询将会被执行。

But if I run the same query in SQL Server it will be executed.

谢谢， 迪内希

推荐答案

考虑使用罗斯文这个非常简单的更新语句：

Consider this very simple UPDATE statement using Northwind:

UPDATE Categories
   SET Description = (
                      SELECT DISTINCT 'Anything' 
                        FROM Employees
                     );

它失败，出现错误操作必须使用一个可更新的查询。

It fails with the error 'Operation must use an updateable query'.

Jet数据库引擎的简单使用了设置一个标量子查询不支持SQL-92语法条款。

The Access database engine simple does not support the SQL-92 syntax using a scalar subquery in the SET clause.

Jet数据库引擎拥有自己的专利 UPDATE..JOIN..SET 语法，但是不安全的，因为不像一个标量子查询，它不需要值是毫不含糊。如果值是不明确的话，发动机无声的挑选一随意，这是很难（如果不是不可能的话）哪一个，即使你是意识到了这个问题将被应用predict。

The Access database engine has its own proprietary UPDATE..JOIN..SET syntax but is unsafe because, unlike a scalar subquery, it doesn’t require values to be unambiguous. If values are ambiguous then the engine silent 'picks' one arbitrarily and it is hard (if not impossible) to predict which one will be applied even if you were aware of the problem.

例如，考虑到现有的分类表中罗斯文及以下愚蠢的（非）表作为目标的更新（愚蠢的，但简单清楚地说明问题）：

For example, consider the existing Categories table in Northwind and the following daft (non-)table as a target for an update (daft but simple to demonstrate the problem clearly):

CREATE TABLE BadCategories
(
 CategoryID INTEGER NOT NULL, 
 CategoryName NVARCHAR(15) NOT NULL
)
;
INSERT INTO BadCategories (CategoryID, CategoryName) 
   VALUES (1, 'This one...?')
;
INSERT INTO BadCategories (CategoryID, CategoryName) 
   VALUES (1, '...or this one?')
;

现在的更新：

UPDATE Categories 
       INNER JOIN (
                   SELECT T1.CategoryID, T1.CategoryName
                     FROM Categories AS T1
                   UNION ALL 
                   SELECT 9 - T2.CategoryID, T2.CategoryName
                     FROM Categories AS T2
                  ) AS DT1
       ON DT1.CategoryID = Categories.CategoryID
   SET Categories.CategoryName = DT1.CategoryName;

当我运行此我听说两行已更新，有趣的，因为只有一个匹配行中的类别表。其结果是，分类表类别ID 现在拥有了......还是这个呢？值。我怀疑它一直是一个竞赛，看看哪个值被写入表中最后一个。

When I run this I'm told that two rows have been updated, funny because there's only one matching row in the Categories table. The result is that the Categories table with CategoryID now has the '...or this one?' value. I suspect it has been a race to see which value gets written to the table last.

在SQL-92标量子查询是冗长的，当有在设置多个条款和/或WHERE子句相匹配的设置的条款，但至少它消除歧义（加上一个体面的优化器应该能够检测到子查询是接近的比赛）。在SQL-99标准中引入了合并可用于消除上述重复但不用说Access不支持，要么。

The SQL-92 scalar subquery is verbose when there are multiple clauses in the SET and/or the WHERE clause matches the SET's clauses but at least it eliminates ambiguity (plus a decent optimizer should be able to detects that the subqueries are close matches). The SQL-99 Standard introduced MERGE which can be used to eliminate the aforementioned repetition but needless to say Access doesn't support that either.

在Access数据库引擎的缺乏对SQL-92标量子查询语法的支持对我来说是最糟糕的设计特点（读错误）。

The Access database engine's lack of support for the SQL-92 scalar subquery syntax is for me its worst 'design feature' (read 'bug').

另外请注意Jet数据库引擎的专有 UPDATE..JOIN..SET 语法不能无论如何与集合函数（总计查询在Access-说吧）使用。看到基于总计更新查询查询失败。

Also note the Access database engine's proprietary UPDATE..JOIN..SET syntax cannot anyhow be used with set functions ('totals queries' in Access-speak). See Update Query Based on Totals Query Fails.

这篇关于操作必须使用一个可更新的查询。 （错误3073）的Microsoft Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！