Updating multiple records with conditions

[davidevan]

I'm trying to update a single field in a table according to the an age
category as a condition.

The meat of the code that executes looks like this (I created a record
set, and loop through it until there are no records):

Code:

Do While Not rstRecSet.EOF

' gets the difference between todays date and db player date
intDifference = DateDiff("yyyy" , CDate(rstRecSet ("BIRTHDATE" )), Date)

If intDifference <= 8 Then
SQLUpdate = "UPDATE Player SET DIVISION = 'LEARN TO PLAY';"
ElseIf intDifference <= 10 Then
SQLUpdate = "UPDATE Player SET DIVISION = 'MITES';"
etc.....
End If

' execute my sql statement here to update record
oC.execute(SQLU pdate)

' move to next record to update....
rstRecSet.MoveN ext Loop

I did a response.write for the different in age and it shows the
difference between ages for all entries in the database (good), but it
will only update the first entry and set every single entry in the
database according to the first entry (so if the age difference is 17,
it will set the division properly, but every single record is going to
have that division name, regardless of their age category from the
first to last record).

Am I out to lunch here, on the right track? Is there possibly a better
way of doing this that I'm unaware of? An SQL update statement with
conditions?
Any sort of help would be appreciated.

[Mike Brind]

"davidevan" <de********@gma il.comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

I'm trying to update a single field in a table according to the an age
category as a condition.

The meat of the code that executes looks like this (I created a record
set, and loop through it until there are no records):

Code:

Do While Not rstRecSet.EOF

' gets the difference between todays date and db player date
intDifference = DateDiff("yyyy" , CDate(rstRecSet ("BIRTHDATE" )), Date)

If intDifference <= 8 Then
SQLUpdate = "UPDATE Player SET DIVISION = 'LEARN TO PLAY';"
ElseIf intDifference <= 10 Then
SQLUpdate = "UPDATE Player SET DIVISION = 'MITES';"
etc.....
End If

' execute my sql statement here to update record
oC.execute(SQLU pdate)

' move to next record to update....
rstRecSet.MoveN ext Loop

I did a response.write for the different in age and it shows the
difference between ages for all entries in the database (good), but it
will only update the first entry and set every single entry in the
database according to the first entry (so if the age difference is 17,
it will set the division properly, but every single record is going to
have that division name, regardless of their age category from the
first to last record).

Am I out to lunch here, on the right track? Is there possibly a better
way of doing this that I'm unaware of? An SQL update statement with
conditions?
Any sort of help would be appreciated.

You need a WHERE clause in your SQL to restrict the update operation to just
the record you are focused on.

UPDATE Player SET DIVISION = 'MITES' WHERE <identity=
<theplayerbeing processed>

--
Mike Brind