Skip to main content
C
cftmfl
Participant
March 28, 2008
Question

Looping an Update Query

  • March 28, 2008
  • 2 replies
  • 328 views
Hi,

This seems like it should be pretty straightforward but I'm new to ColdFusion and cannot get this working at all.

I want to update a column in a database with an incremented variable in a loop. I want to be able to do this for a date column eventually (increment to next day) but I cannot get even this basic query below to work.

The query is attached below

What I want this to do is loop through the Team table starting at row 1 and update the players shirt number from 1 to 11.

At the moment all shirt numbers are set to 11.

Any guidance on this would be greatly appreciated.

thanks in advance

This topic has been closed for replies.

2 replies

N
Newsgroup_User
Inspiring
March 28, 2008
cftmfl wrote:
> <cfset playerShirt = 1>
>
> <cfloop condition = "playerShirt LTE 11">
> <cfquery name = "UpdateTeam" datasource = "#dsn#">
> Update Team
> set Shirt = #playerShirt#
> <cfset playerShirt = playerShirt + 1>
> </cfquery>
> </cfloop>

To expand on JR's answer. Since your query does not have a where
clause, each time through the loop all records in the 'Team' table have
their 'Shirt' field set to the current value of 'playerShirt'. I.E. You
update all the fields 11 times with the last update placing '11' in all
the records.

This is a basic SQL concept that has nothing specific to do with
ColdFusion other then ColdFusion is a very easy way to interact with a
database using SQL.

Checking out some good introductory SQL resources such as W3Schools
[ http://www.w3schools.com/sql/default.asp or 'Teach Yourself SQL in 10
minutes' by Ben Forta [ http://www.forta.com/books/0672325675/ could be
very helpful.





C
cftmflAuthor
Participant
March 28, 2008
Thanks for the answers guys....was looking at too long and completely missed the obvious! thanks again...
J
JR__Bob__Dobbs-qSBHQ2
Inspiring
March 28, 2008
Since you do not have a WHERE clause in your SQL statement each iteration of the loop will set all shirt values to #playerShirt#
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices