Operation must use an updatable query. (Error 3073)

Question

I have written this query:

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

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

Thanks, dinesh

Solution 1

I'm quite sure the JET DB Engine treats any query with a subquery as non-updateable. This is most likely the reason for the error and, thus, you'll need to rework the logic and avoid the subqueries.

As a test, you might also try to remove the calculation (the subtraction) being performed in each of the two subqueries. This calculation may not be playing nicely with the update as well.

Solution 2

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

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

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

Now for the 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;

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.

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.

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

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.

Solution 3

Keep in mind that if you copy over a query that originally had queries or summary queries as part of the query, even though you delete those queries and only have linked tables, the query will (mistakenly) act like it still has non-updateable fields and will give you this error. You just simply re-create the query as you want it but it is an insidious little glitch.

Comments

[onedaywhen]

-1 The Access database engine simply does not support the SQL-92 'scalar subquery' syntax.

[David-W-Fenton]

Depends on the subquery. Subqueries in WHERE clauses do not have that effect. Subqueries in the FROM may or may not, depending on various issues. The main issue is whether or not Jet/ACE can resolve the relationship type so that it is unambiguously told which single record to update. One thing to try if you're running the SQL in Access itself is the DISTINCTROW predicate, which can sometimes make nonupdatable queries updatable.

[onedaywhen]

"The main issue is whether or not Jet/ACE can resolve the relationship type so that it is unambiguously told which single record to update" -- well, the Access database engine's UPDATE..JOIN syntax doesn't require unambiguous values: in fact if it does produce ambiguous values it will silently pick one arbitrarily and it is hard to predict which one will be picked (our old favourite about PKs and clustering on disk, I suspect). The lack of support for scalar subqueries in the UPDATE SET clause is IMO the Access database engine's worst 'design feature' (read 'bug').

[onedaywhen]

-1 The Access database engine simply does not support the SQL-92 'scalar subquery' syntax.

[David-W-Fenton]

Why is that lack in Jet/ACE justify downvoting this answer, which is, in fact, a viable solution to the problem? Seems to me your quibble is with Jet/ACE, not with this answer.

[David-W-Fenton]

@onedaywhen: why are you downvoting answers when you actually mean to be downvoting Jet/ACE?

[David-W-Fenton]

While this is all very interesting, it's not a solution to the problem, of which there are many. Every database engine has cases where statements that ought to be theoretically updatable are not, so it's really quite churlish of you to make such a big deal of this known and documented behavior of the Jet/ACE SQL dialect.

[onedaywhen]

@David-W-Fenton: I rather think you do know what my solution to the "Access can't even perform a SQL-92 UPDATE problem" is ;)

[onedaywhen]

@David-W-Fenton: The question is essentially, "Why does my SQL Server query not run in Access (ACE, Jet, whatever)?" Changing the Recordset Type misses the point entirely, hence the downvote. As for downvoting Jet/ACE, if only it were that easy; it rather takes about a decade of informing people daily about its many flaws and I stand by my assertion, "The Access database engine's lack of support for the SQL-92 scalar subquery syntax is for me its worst 'design feature' (read 'bug')."

[onedaywhen]

@David-W-Fenton: "Every database engine has cases [of views] that ought to be theoretically updatable are not" -- I was today reminded of a great quote that dates from 1693: "The duke of Ormond took a view yesterday of his troop, and ordered all that had bay or grey horses to change them for black", thought to be the earliest known example of view updating ;)