Aggregate function in CF 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>

 

[ram123]

try this one

<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;>
<cfset mymaxid = #ID# - 1>
</cfquery>

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

UPDATE Inventory
SET ID = #mymaxid#
WHERE item_number = #URL.ItemNo#

</cfquery>

 

[khazmir]

The aggregate error is referring to your use of &quot;MAX&quot; in the ResetInventory query. Remove MAX from your set statement.

You may also get some additional errors such as not closing the quotes around your cfquery name.

 

[programmher]

ram123,

THAT IS IT!!!!!

Thanks sooooooooooo much! This one had me stumped - it was so simple that I was going everywhichway but where I needed to go!

I tried using a <CFSET> statement before - just not the way your example had it.

(That enormous sigh you just heard was my sigh of relief and that shaking you just felt was me doing a &quot;Holy Dance&quot; of jublilation!!!)