Operation must use an updateable query

[Rik_StHelens]

Hi all,

I am trying to execute a query to update a table with pricing values calculated in a seperate select query.

This comes back with an error messahe of "Operations must use an update query". I have checked that the database and linked data files are not in read only folders, but still get the same message.

Code is as follows:

UPDATE 150_Pricing_Report, GardnersFile SET GardnersFile.PriceOnAmazonUK = [150_Pricing_Report].[Amazon_Sellprice], GardnersFile.PriceOneBayUK = [150_Pricing_Report].[Ebay_Sellprice], GardnersFile.PriceOnPlaycom = [150_Pricing_Report].[Play_Sellprice];

Can anyone help on this please, as its driving me nuts!

Thanks in advance.

Richard

 

[plog]

First, UPDATE queries always sets off my alarms. Why must this data be updated? Will this data be updated frequently? Are you storing a history of updates?

Second, I don' think your database, specifically GardnersFile, is normalized. Field names shouldn't contain specific information about the data they hold (i.e. 'PriceOnAmazonUK', '...OneBayUK'). What happens when you want to store a price from another location? The answer shouldn't be you add a new column, the answer should be you add a new row. That means moving the name of the location into its own field.

Third, your UPDATE query says it is trying to update 2 tables ('UPDATE 150_Pricing_Report, GardnersFile...'), but in the SET clause you are only updating fields from GardnersFile. It seems you are trying to update it with data from 150_Pricing_Report. Only the table being updated should be listed after the UPDATE keyword.

Fourth, you haven't established a link between the two tables in your query. If this did work, all the rows of data in GardnersFile would contain the same value. My guess is you need a WHERE clause in there to establish a link between your two tables. Most likely on a product id or (god forbid) a product name.

Check out this link for how to write an UPDATE query: http://www.w3schools.com/Sql/sql_update.asp

 

[Rik_StHelens]

Hi Plog,

Thanks for your reply, I have added responses as follows to give a bit more information:

1. This is a one off update of a file which sits outside this database as standard. This database (which I inherited from someone who no longer wants to support his customer) is used purely to calculate markup, and exports that mark up to a text file once a night to be imported into a seperate inventory management system. After a recent big change to our discount levels the easiest way to update pricing in the inventory system, is to export the inventory file and link it to this database and run a query, and reimport it. It might seem a bit backwards, and in the long run it will change, but we need a quick fix at this moment in time.

2. Again you are right, although these fields do not exist as a table in the DB, and are just output from a query to a text file once a night. Again this will change when we have time to sit down and do a redesign of the system.

3. My bad, I was playing around with copies of tables and forgot to add them back in before copying the SQL code, actual code as follows:

UPDATE 150_Pricing_Report RIGHT JOIN GardnersFile ON [150_Pricing_Report].ISBN13 = GardnersFile.ISBN SET GardnersFile.PriceOnAmazonUK = [150_Pricing_Report].[Amazon_Sellprice], GardnersFile.PriceOneBayUK = [150_Pricing_Report].[Ebay_Sellprice], GardnersFile.PriceOnPlaycom = [150_Pricing_Report].[Play_Sellprice]
WHERE (((GardnersFile.ISBN)=[150_Pricing_Report].[ISBN13]));

4. As point 3.

Still brings back the same error message, and hopefully this makes it a bit clearer and will help to come up with a solution?

Thanks again!

 

[plog]

Is 150_Pricing_Report a table or a query? Why did you make it a RIGHT JOIN?

 

[Rik_StHelens]

Is 150_Pricing_Report a table or a query? Why did you make it a RIGHT JOIN?

Yes pricing report is a query, and uses a formulae to calculate prices based on Wholesale Price, RRP, Base margin and % margin for each marketplace (Amazon, Ebay, Play)

As for the RIGHT JOIN, I tried left, right and no joins just to see if it helped....

 

[plog]

You should update the underlying table of the query, not the query. That's probably the reason for the error.

 

[jdraw]

A couple of points to consider:

Update queries are quite unforgiving. That is if you don't get the criteria correct, you could end up adjusting a lot of records (the wrong records). Ant there's no simple UNDO command. Bottom line, set up a select query first to make sure you are selecting the proper records. Then do the update.

But as plog has said w3schools has tutorials/examples.

If a query is not updateable there can be several reasons.
see http://allenbrowne.com/ser-61.html

 

[Rik_StHelens]

You should update the underlying table of the query, not the query. That's probably the reason for the error.

Does that also apply when I am solely using that underlying query, in the update query, to update a different table.

If a query is not updateable there can be several reasons.
see http://allenbrowne.com/ser-61.html

The sub query I am using which calculates the selling prices uses the Round() function to limit the result to 2 decimal points. This could also be part of the problem too?

Also as per your other point, I am running a test update against a copy file BEFORE applying the update to the actual stock file so that we can verify prices. The way in which the file was exported ensures that only records which need to be updated are included in the file (because it is only products from one supplier, and they have changed our discount across all their product ranges)

 

[jdraw]

Good that you are doing a select query to select the proper records.
Please post the SQL for that Select query.

Can you tell us in plain English what the update statement is to do?

eg. I want to update the mileage for each of the Red cars that have received an annual maintenance checkup in the last 30 days....

I agree that your process seems quite backward (and convoluted) but you know your business better than we do.

I'm not sure why you don't have a "current discount" field to be used in calculations for products from that supplier.