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

问题描述

我写了这个查询:

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';

我收到此错误消息:

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.

谢谢，饭菜

推荐答案

考虑这个使用 Northwind 的非常简单的 UPDATE 语句:

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'.

Access 数据库引擎 simple 不支持在 SET 子句中使用标量子查询的 SQL-92 语法.

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

Access 数据库引擎有自己专有的 UPDATE..JOIN..SET 语法，但不安全，因为与标量子查询不同，它不需要明确的值.如果值不明确，那么引擎会沉默地任意选择"一个，即使您意识到问题，也很难(如果不是不可能)预测将应用哪个.

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.

例如，考虑 Northwind 中现有的 Categories 表和以下愚蠢的(非)表作为更新目标(愚蠢但简单以清楚地说明问题):

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:

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;

当我运行它时，我被告知有两行已更新，有趣的是因为 Categories 表中只有一个匹配的行.结果是带有 CategoryID 的 Categories 表现在有...还是这个?"价值.我怀疑查看哪个值最后写入表是一场竞赛.

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.

当 SET 中有多个子句和/或 WHERE 子句与 SET 的子句匹配时，SQL-92 标量子查询是冗长的，但至少它消除了歧义(加上一个体面的优化器应该能够检测到子查询是接近匹配的).SQL-99 标准引入了 MERGE 可用于消除上述重复，但不用说 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').

另请注意，Access 数据库引擎的专有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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！