Help with updating databse with cfloop

[Guest]

I want to move data from one column to another. I thought I could just query points2 column then use a cfloop to update the data to points1 column. Finally null out points2 column.

Below is my code on the first 2 steps. Unfortunately, I get the following error:

Syntax error in UPDATE statement.

The error occurred in wwwroot\forms\changepoints.cfm Line 18

<cfquery name="getPoints" datasource="#dsn2#">

SELECT leaderID, points2

from leaderboard

</cfquery>

<cfloop query="getPoints" startrow=1 >

  <CFQUERY NAME="DoUpdate" DATASOURCE="#dsn2#">

       UPDATE leaderboard

       SET points1=#getPoints.points2#

      WHERE leaderID=#getPoints.leaderID#    (line 18)

    </CFQUERY>

</cfloop>

What am I doing wrong?

[Dan_Bracuk]

Logically, this seems to be the same as what your attempting:

update leaderboard

set points1 = points2

Unless I'm missing something, why don't you simply do that?

[Guest]

Without using the leaderID, which is the individual players, are the points going to go the right rows?

[Steve Sommers]

RE: Without using the leaderID, which is the individual players, are the points going to go the right rows?

Yes. And it would be much more efficient since you are not sending data to CF and back to the SQL server. You can even add the "where points2 is not null" clause, if needed.

[Steve Sommers]

I agree with Dan for this example. But to your specific error, most likely one or more records in your leaderboard table has a null value for points2 or leaderID.

[Guest]

I ran a screen dump and noticed there are 3 empty strings, not all players will have points. I also noticed that the leaderID are not in order. I can't have points belonging to one player end up going to someone else

[Guest]

I added the where points2 is not null to the query and all is well. Thanks.

[Carl Von Stetten]

@rickaclark54,

In your revised code, are you still running a select query and then running update queries in a loop as before?  If so, you could do the update in one query and eliminate the loop entirely.  Maybe you can post your revised code.

-Carl V.

[Guest]

Hi Carl, below is my revised code:

<cfif isdefined("operation")>

<cfif #form.operation# IS "Move Osceola Points">   

<!--- query to get all points --->

<cfquery name="getPoints" datasource="#dsn#">

SELECT leaderID, points2

from leaderboard

where (points2 IS not NULL) AND (tournID = 1)

</cfquery>

<!--- loop the points into the new column: points2 --->

<cfloop query="getPoints" startrow=1 >

  <CFQUERY NAME="DoUpdate" DATASOURCE="#dsn#">

       UPDATE leaderboard

       SET points1=#getPoints.points2#

       WHERE (leaderID=#getPoints.leaderID#) AND (tournID = 1)

    </CFQUERY>

</cfloop>

<!--- clear points column of all points --->

<cfquery name="updateLeaderboard" datasource="#dsn#">

UPDATE  leaderboard

SET  points2 = NULL

where tournID = 1

</cfquery>

</cfif>

[Carl Von Stetten]

Like Dan said before, I think you could replace this:

<!--- query to get all points --->

<cfquery name="getPoints" datasource="#dsn#">

SELECT leaderID, points2

from leaderboard

where (points2 IS not NULL) AND (tournID = 1)

</cfquery>

<!--- loop the points into the new column: points2 --->

<cfloop query="getPoints" startrow=1 >

  <CFQUERY NAME="DoUpdate" DATASOURCE="#dsn#">

       UPDATE leaderboard

       SET points1=#getPoints.points2#

       WHERE (leaderID=#getPoints.leaderID#) AND (tournID = 1)

    </CFQUERY>

</cfloop>

With this:

<cfquery name="updatePoints" datasource="#dsn#">

UPDATE leaderboard

SET points1=points2

WHERE (points2 IS NOT NULL) AND tournID =1

</cfquery>

Update queries run record by record, so you don't have to worry about it scrambling the values between rows (which you expressed as a concern in reply to Dan).  It's best to let the database handle this rather than by looping in ColdFusion - it will be much more efficient that way.

-Carl V.

[Carl Von Stetten]

In fact, you should be able to eliminate the last query by combining it with the one Dan and I suggested:

<cfquery name="updatePoints" datasource="#dsn#">

UPDATE leaderboard

SET points1=points2, points2=NULL

WHERE (points2 IS NOT NULL) AND tournID =1

</cfquery>

This will be safe because when you do an UPDATE in most databases, it loads the record as it currently is into memory as a snapshot, makes the changes, then writes it back to the database.  So while the update is processing, the points2 hasn't been reset to NULL yet, and the value can be copied into points1.

-Carl V.