Update query using a sub select

Hi Guys, I have having trouble updating a table with the result of a query to
sum a value.

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

If you have any ideas at all I'd be very grateful.

Hi Admingod,

view this Kb
-----------------
ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default...;116142&Produc...
----------------

bye
--
Giorgio Rancati
[Office Access MVP]

"Admingod" ha scritto nel messaggio
...
Hi Guys, I have having trouble updating a table with the result of a query
to
sum a value.

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

If you have any ideas at all I'd be very grateful.

On Feb 12, 8:28 pm, "giorgio rancati"
wrote:
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
-----------------
ACC: Update Query Based on Totals Query Failshttp://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc...
----------------

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.

--

Thanks guys! I don't feel good about access SQL right now, but you both
answered my question eloquently.

"Jamie Collins" wrote:

On Feb 12, 8:28 pm, "giorgio rancati"
wrote:
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
-----------------
ACC: Update Query Based on Totals Query Failshttp://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc...
----------------

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.

--

On Feb 13, 4:25 pm, Admingod
wrote:
I don't feel good about access SQL right now, but you both
answered my question eloquently.

There's good and there's bad, of course. Ironically for me, what I
consider to be the very best stuff (the DECIMAL data type, table-level
CHECK constraints, cycle resolution in CASCADE referential actions,
etc) people seem to avoid, seemingly for no good reason, and some of
the bad stuff (UPDATE syntax, JOIN conditions not supported,
constraints not deferrable, etc) are unavoidable fundamental
operations that one is forced to 'jump through hoops' to achieve.
Makes for an interesting product, though ;-)

Jamie.

--