Operation must use an updateable Query?

[tonygg]

Hi. I am having trouble updating a table using a compound query. I have done these before (no so complex ones) without trouble.

The table (UserDefinedProperties) contains a number of rows per Product. I am first finding the rows and comparing values to come up with a list of rows that need updating as well as the new value to update the required cell. Good news is this query FULLY WORKS and brings back all the correct information when run standalone. The query is quite complex and uses grouping to create a single row of values from the UserDefinedProperties table to use for the update.

basically this query returns 2 key things. The "UserDefinedProperties.nID" (the primary key of the row to be updated) and the new value to be updated on in the row "PreCutProds.PreCutCatFinal" (text value to be used to update UserDefinedProperties.sValue). The working code is:

SELECT UserDefinedProperties.nID, UserDefinedProperties.sValue, UserDefinedProperties.SContentID, PreCutProds.[Short description], PreCutProds.ManPrecut, PreCutProds.PrecutCat, PreCutProds.PreCutCatFinal, PreCutProds.TypePrecutnIDFROM UserDefinedProperties INNER JOIN (SELECT First(Product.[Short description]) AS [Short description], Product.[Product Reference], First(UDP.SContentID) AS FirstOfSContentID, First(UDP.sValue) AS FirstOfsValue, First(UDP.sName) AS FirstOfsName, First(UDP.nID) AS FirstOfnID, Max(IIf(UDP.sName='Type of Precut',UDP.sValue,Null)) AS TypePrecut, Max(IIf(UDP.sName='Type of Precut',UDP.nID,Null)) AS TypePrecutnID, Max(IIf(UDP.sName='Category',UDP.sValue,Null)) AS Precut, Max(IIf(InStr(Product.[Short description],'Jelly Roll')>0,'JELLY ROLL',IIf(InStr(Product.[Short description],'Charm Pack')>0,'CHARM PACK',IIf(InStr(Product.[Short description],'Layer Cake')>0,'LAYER CAKE',Null)))) AS PrecutCat, Max(IIf(UDP.sName='brand',UDP.svalue,Null)) AS ManPrecut, 
IIf(IsNull(PrecutCat),
iif(instr(ManPrecut, 'Hoffman')>0, 'HOFFMAN', 
iif(instr(ManPrecut, 'Lewis & Irene')>0, 'LEWIS & IRENE', 'OTHER')),PrecutCat) AS PreCutCatFinal
FROM Product INNER JOIN (SELECT  UserDefinedProperties.nID, UserDefinedProperties.SContentID, UserDefinedProperties.sValue, Variable.sName, UserDefinedProperties.nID
   FROM UserDefinedProperties LEFT JOIN Variable ON UserDefinedProperties.nVariableID = Variable.nID 
   WHERE ((Variable.sName='Category' and UserDefinedProperties.sValue = 'PRE-CUT') or Variable.sName='Type of Precut' or Variable.sName='brand')  AND UserDefinedProperties.nContentLevel = 2 )  AS UDP ON Product.[Product Reference] = UDP.SContentID
GROUP BY Product.[Product Reference]
HAVING (((Product.[Product Reference]) Not Like '*!*') AND ((Max(IIf([UDP].[sName]='Category',[UDP].[sValue],Null))) Is Not Null))
ORDER BY First(Product.[Short description])
)  AS PreCutProds ON UserDefinedProperties.nID = PreCutProds.TypePrecutnID
WHERE UserDefinedProperties.sValue <> PreCutProds.PreCutCatFinal and UserDefinedProperties.nID = PreCutProds.TypePrecutnID;

I have saved the query as "QUERY3" just to make it easier to read in this question. I am using this as part of an update query:

UPDATE UserDefinedProperties INNER JOIN QUERY3 ON UserDefinedProperties.nID = Query3.nID SET UserDefinedProperties.sValue = Query3.PreCutCatFinal;

I can save the resulting query and run it but get the error message: "Operation must use an updateable Query".



I am a loss to see what that means in this context and why it is not updateable? Is it because I use "UserDefinedProperties" (the table I want to update) in query 3?

Any help or advice would be greatly appreciated.

Many thanks

Tony

[ranman256]

some queries , due to joins, cannot save.
either:
reduce the joins so you can update,
or
if you must, write to a 'temp' table, then update from that.

you CANNOT sum and update in the same query chain. You must use the temp table method. Sum to the table, then update from that.

[orange]

Also look at this material from Allen Browne

[John_G]

Never mind - I was thinking of something else. Check the link in Orange's post.