Stuck on Query

[EASman]

Can someone tell me why this Query won't work? I think my syntax is wrong somewhere but not sure what it is.


UPDATE ct_ProductClassification
SET ProductClassificationName = (SELECT
CategoryCodeTemp.CategoryDescription
FROM
CategoryCodeTemp,ct_productclassification
WHERE
CategoryCodeTemp.CompanyNumber = ct_ProductClassification.BusinessUNitFK
AND
CategoryCodeTemp.CategoryCode = ct_ProductClassification.ProductClassificationID)
WHERE
ct_ProductClassification.ParentClassificationkey <> '0';

When I try to run this it says it must be an updatable Query.

Thanks [sig][/sig]

 

[MichaelRed]

EASman,

not entirely clear from you SQL, however the error generally is saying that one (or more) of the objects in the FROM clause cannot be updated. It doesn't matter (to SQL) wheather the table/field you are trying to update may be updated or not, just that SOME table/field cannot be updated. This often occurs because one of the sources (object listed in the FROM clause) is a query with a one:many relationship - at least SOMEWHERE in it's geneaalogy.

The soloution may be to do a maketable query for the offending object, and create a NEW table each time you need to do the update. This, of course, depends on the details of the process - and may not be suitable for you exact situation.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]