1 Reply Latest reply on May 30, 2006 12:12 PM by jdeline

    update based on ordered data

      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
      <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#