Skip to main content
A
Anonymous
November 8, 2012
Question

Help with updating databse with cfloop

  • November 8, 2012
  • 2 replies
  • 1586 views

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?

    This topic has been closed for replies.

    2 replies

    S
    Steve Sommers
    Legend
    November 8, 2012

    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.

    A
    Anonymous
    November 8, 2012

    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

    A
    Anonymous
    November 8, 2012

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

    D
    Dan_Bracuk
    Inspiring
    November 8, 2012

    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?

    A
    Anonymous
    November 8, 2012

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

    S
    Steve Sommers
    Legend
    November 8, 2012

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices