The sql is:
UPDATE Invoices SET Invoices.SubTotalFees = (SELECT DISTINCTROW
Sum([InvoiceMatterSubTotalFees].[SumOfFee]) AS [Sum Of SumOfFee]
FROM InvoiceMatterSubTotalFees; )
WHERE (((Invoices.InvoiceNo)=[Forms]![CreateInvoiceDialog]![InvoiceNo]));
Its really simple, I just want to update the Invoice field with the sum of
some charges which I collect from the select statement.
When I run it I get, "operation must be an updatable query".
view this Kb
Just in case the OP missed the point, Jet's UPDATE syntax is pretty
awful.
The OP's SQL syntax looks fine as regards the standard SQL-92 syntax
i.e.
UPDATE <table>
SET <column> = (<scalar subquery>)
Sadly, Jet SQL does not comply with the SQL-92 standard. It has its
own proprietary and unpredictable syntax:
UPDATE <table>
<> JOIN <other table>
<join condition>
SET <column> = <join column>
The syntax is unpredictable because the <column> to <join column>
could be one to many so which one of the many does Jet pick? There is
no spec for Jet and I don't think anyone can say the answer to this
puzzle.
The trouble is, even Jet's proprietary and unpredictable syntax won't
work if the 'other table' contains a set function. Let's use a very
simple and contrived test (non)table:
CREATE TABLE Test (
col1 INTEGER,
col2 INTEGER
)
;
INSERT INTO Test VALUES (1, 1)
;
INSERT INTO Test VALUES (2, 1)
;
INSERT INTO Test VALUES (2, 2)
;
INSERT INTO Test VALUES (3, 1)
;
INSERT INTO Test VALUES (3, 2)
;
INSERT INTO Test VALUES (3, 3)
;
The aim is to UPDATE col2 to be the maximum value when we GROUP BY
col1. The correct values are easily identified with a correlated
subquery:
SELECT col1, col2 AS old_value, (
SELECT MAX(T2.col2)
FROM Test AS T2
WHERE Test.col1 = T2.col1
) as new_value
FROM Test;
which is easily converted into a SQL-92 UPDATE:
UPDATE Test
SET col1 = (
SELECT MAX(T2.col2)
FROM Test AS T2
WHERE Test.col1 = T2.col1
);
Oops! We get the dreaded, 'Operation must use an updateable query'
error. We know Jet instead wants a JOIN:
SELECT * FROM
Test
INNER JOIN
(
SELECT T2.col1, MAX(T2.col2) AS max_col2
FROM Test AS T2
GROUP BY T2.col1
) AS DT1
ON Test.col1 = DT1.col1;
Re-write as an UPDATE:
UPDATE
Test
INNER JOIN
(
SELECT T2.col1, MAX(T2.col2) AS max_col2
FROM Test AS T2
GROUP BY T2.col1
) AS DT1
ON Test.col1 = DT1.col1
SET col2 = DT1.max_col2
;
Oops! 'Operation must use an updateable query' again. It doesn't like
the derived table DT1 but can we fool it with a virtual table (VIEW)?
CREATE VIEW DT1
AS
SELECT T2.col1, MAX(T2.col2) AS max_col2
FROM Test AS T2
GROUP BY T2.col1
;
UPDATE
Test
INNER JOIN
DT1
ON Test.col1 = DT1.col1
SET col2 = DT1.max_col2
;
No, same error as before. This is not really surprising when you
consider VIEWs in Jet are not materialized; rather, the SQL definition
of the VIEW is 'pasted in' as a derived table.
The KB article suggests using a permanent (the Help may suggest
otherwise but AFAIK Jet does not support temp tables) staging table to
load the values:
DROP VIEW DT1
;
CREATE TABLE DT1 (
col1 INTEGER,
max_col2 INTEGER
)
;
INSERT INTO DT1 (col1, max_col2)
SELECT T2.col1, MAX(T2.col2) AS max_col2
FROM Test AS T2
GROUP BY T2.col1
;
UPDATE
Test
INNER JOIN
DT1
ON Test.col1 = DT1.col1
SET col2 = DT1.max_col2
;
Not very impressive having to use a staging table to do a simple
UPDATE
I wonder if there is a case in Jet to denormalize in order to be able
to use UPDATE...
Jamie.
