views:

24

answers:

2

I'm trying to run a very simple update query, like so:

UPDATE tblSkuActueel
INNER JOIN qrySkuTotaal ON tblSkuActueel.sku = qrySkuTotaal.sku
SET tblSkuActueel.stock = [qryskutotaal].[stockaantal];

As you can see, it has to update the table. It should not have any problems doing so.

Yet, I get the error message that I'm trying to edit an uneditable query.

BOTH the tabel and the query are editable, and even then, it only needs to update the data in the table with the data from the query. How can I fix this?

A: 

Is there a unique index on either table?

Remou
And does qrySkuTotaal produce a single row for each SKU value?
David-W-Fenton
A: 

It's a long shot, but give the Jet/ACE-only keyword DISTINCTROW a try:

  UPDATE DISTINCTROW tblSkuActueel
  INNER JOIN qrySkuTotaal ON tblSkuActueel.sku = qrySkuTotaal.sku
  SET tblSkuActueel.stock = [qryskutotaal].[stockaantal];

If that doesn't work (and it likely won't), and qrySkuTotaal returns a single record per SKU, then you're out of luck, and will likely have to use a correlated subquery to get the update value (which is likely to be much slower), or a temp table for the totals (which, believe it or not, will likely be faster even with the overhead of creating the temp table).

David-W-Fenton