EasyCFM.COM ColdFusion Forums / Coding Help! / Help with updating columns using cfloop

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Help with updating columns using cfloop

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

rickaclark
11-08-2012 @ 9:45 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

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?

cfSearchin
11-08-2012 @ 10:51 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

You do not need a loop to update all records in the same table. A single update should do it. (Always backup your data first).


   UPDATE leaderboard
   SET    points1 = points2,
          points2 = NULL



rickaclark
11-08-2012 @ 12:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

Thanks, I'll give it a try.

Cfdevshop
03-19-2013 @ 12:10 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Mar 2013

here,no need to use two query and loop
you can do with single query.

  <CFQUERY NAME="DoUpdate" DATASOURCE="#dsn2#">
       UPDATE leaderboard
       SET points1=points2      
  </CFQUERY>



Coldfusiondevshop


Website Designed and Developed by Pablo Varando.