10 Replies Latest reply on Nov 8, 2012 12:13 PM by Carl Von Stetten

    Help with updating databse with cfloop

    rickaclark54 Level 1

      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?

        • 1. Re: Help with updating databse with cfloop
          Dan Bracuk Level 5

          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?

          • 2. Re: Help with updating databse with cfloop
            rickaclark54 Level 1

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

            • 3. Re: Help with updating databse with cfloop
              Steve Sommers Level 4

              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.

              • 4. Re: Help with updating databse with cfloop
                rickaclark54 Level 1

                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

                • 5. Re: Help with updating databse with cfloop
                  rickaclark54 Level 1

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

                  • 6. Re: Help with updating databse with cfloop
                    Steve Sommers Level 4

                    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.

                    • 7. Re: Help with updating databse with cfloop
                      Carl Von Stetten Adobe Community Professional & MVP

                      @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.

                      • 8. Re: Help with updating databse with cfloop
                        rickaclark54 Level 1

                        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>

                        • 9. Re: Help with updating databse with cfloop
                          Carl Von Stetten Adobe Community Professional & MVP

                          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.

                          • 10. Re: Help with updating databse with cfloop
                            Carl Von Stetten Adobe Community Professional & MVP

                            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.