Aggregate function in Update Query

[programmher]

How do I resolve this error for the below queries:

[MERANT][ODBC SQL Server Driver][SQL Server]An aggregate may not appear in the set list of an UPDATE statement.

<cfquery name=&quot;View&quot; datasource= MyDatabase>
SELECT MAX (inventory_number) AS ID
FROM tblInventory
WHERE item_number= '#URL.ItemNo#'


</cfquery>

<cfoutput query = &quot;View&quot;></cfquery>

<cfquery name=&quot;ResetInventory datasource=&quot;MyDatabase&quot;>

UPDATE Inventory
SET ID = MAX(#View.ID#-1)
WHERE item_number = #URL.ItemNo#

</cfquery>

 

[tlbroadbent]

The (Cold Fusion?) syntax is unfamiliar to me but it seems that it shouldn't be necessary to use MAX in the Update query when MAX has already been used in the source View.

I would really expect the SQL syntax to be more like the following.

UPDATE Inventory
SET ID = (Select #View.ID#-1 From #View#)
WHERE item_number = #URL.ItemNo#

However, I'm unsure of the proper syntax because the relationship between various objects is unclear and the process flow has not been not specified. Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker