Webmaster Forum / ColdFusion / Advanced Techniques / May 2006

I need to update a field in the database based on an ordered query.  The
problem I am having is that the updated field (targetvalue) is not updating the
right record in the database.  I need the targetvalue to insert into the
database so that the highest targetvalue is with the highest drymatter and the
lowest targetvalue is with the lowest drymatter.

Here is the code so far.  It is creating the targetvalues and updating the
database.  It just isn't doing it in the correct order.

<cfquery name="target" datasource="Dairy">
SELECT *
FROM dairy.dbo.paddockdata
WHERE FarmID='#form.farmid#'
AND DataDate=#DateValue#
ORDER BY drymatter DESC
</cfquery>
<cfset M=(2750-1100)/(0-#form.numrows#)>

<CFLOOP INDEX="onerow" FROM="0" TO="#target.recordcount#">
<cfset targetvalue=(M*(1+#onerow#))+2750>
<CFQUERY NAME="insertvaluedata" DATASOURCE="dairy">
UPDATE dairy.dbo.paddockdata
SET targetvalue= #TargetValue#
WHERE ID=#target.ID#
</CFQUERY>
</CFLOOP>