If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Update query using a sub select
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. |
#3
|
|||
|
|||
Update query using a sub select
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. -- |
#4
|
|||
|
|||
Update query using a sub select
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. -- |
#5
|
|||
|
|||
Update query using a sub select
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. -- |
Thread Tools | |
Display Modes | |
|
|