Operation Must Use An Updatable Query

[warrenk]

When I try to run the following SQL, I get the error message "Operation Must Use A Updatable Query". Any idea how to fix this?

UPDATE suppprice SET suppp_price = (Select price from str_prices where str_prices.brand_name = suppprice.suppp_brand and str_prices.product_url = suppprice.suppp_kurl AND str_prices.product_name = suppprice.suppp_knm)
WHERE company_id=2;

 

[dhookom]

You could try this slow solution

UPDATE suppprice SET suppp_price = DLookup("price","str_prices","brand_name =""" & suppp_brand & """ and product_url = """ & suppp_kurl  & """ AND product_name = """ & suppp_knm & """")
WHERE company_id=2;

A faster solution would depend on Brand_Name, Product_URL, and Product_Name being the primary key of the table str_Prices.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

You may try this:
UPDATE suppprice INNER JOIN str_prices
ON suppprice.suppp_brand = str_prices.brand_name AND suppprice.suppp_kurl = str_prices.product_url AND suppprice.suppp_knm = str_prices.product_name
SET suppprice.suppp_price = str_prices.price
WHERE suppprice.company_id = 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[warrenk]

Thanks again PHV...exactly what I was looking for!