I'm trying to write what should be a simple nested UPDATE SQL statement in Access 2000 to basically do a row count. it should set the 'TotalSold' column in my 'Products' table to the count of the rows in the 'Sales' table, it goes something like...
UPDATE Products SET TotalSold = ( SELECT COUNT(1) FROM Sales WHERE Sales.ProductID = Products.ID );
...but I get the error 'Operation must use an updateable query' even though I have full permission and it works fine when I replace the nested query with '1'.
I have to use an UPDATE query to set the TotalSold column and can't use view queries or anything, any ideas?
Sorry, I thought someone would ask It's for a web based database system that can only display tables and not queries. Writing a front end to display queries would be the textbook approach, but not exactly practical! If an UPDATE query exists to do it then it would make the task 100 times easier, after all I guess all outcomes of an UPDATE query are essentially derived data.
What are you using to develop your front end that doesn't support queries?
All UPDATE queries are not derived data. Usually they are used to set an attribute given a set of parameters.
SET myStatus = 2
WHERE myCriteria = "Something that needs to be set to 2"
The reason it is questionable to rely on update queries to create redundant derived data is consistancy. Everytime a value changes, you must re-populate all of your derived data. There ARE applications for doing this, but they are generally relegated to data warehouse scenarios.
It's an in house ASP vbscript front end, pretty massive with lots of complicated table interfunctionality and will never have to display any query so it's just not worth writting a front end to do it. Plus it's rather mission critical so in future the 'TotalSales' field will be generated by other means, offering useful trouble shooting should it disagree with the Sales table.
If you know it's not possible could you tell me and I'll just write some VBscript to do it.
Of course ASP can display a query, but the reason I asked if the UPDATE query was possible is it offers valuable mission critical trouble shooting information if the so called 'derived' field does not agree with the value it would be 'derived' from. plus it would be 100 times quicker than converting the system to display queries, yes it is that complex a system!!!
*** please could people only post replies to the first request of a solution to the UPDATE query, if I could use SELECT to derive the data I would have. thanks. ***