Looping an Update Query

[cftmfl]

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

[JR__Bob__Dobbs-qSBHQ2]

Since you do not have a WHERE clause in your SQL statement each iteration of the loop will set all shirt values to #playerShirt#

[Newsgroup_User]

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.

[cftmfl]

Thanks for the answers guys....was looking at too long and completely missed the obvious! thanks again...