Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Without using the leaderID, which is the individual players, are the points going to go the right rows?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I added the where points2 is not null to the query and all is well. Thanks.
Copy link to clipboard
Copied
@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.
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.