Unanswered: Updating multiple records with conditions
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):
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';"
' execute my sql statement here to update record
' move to next record to update....
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.
Ok, figured out the problem. Sat down and thought about it, turns out I needed a WHERE clause in those updates. It would always take the last value in the record and whatever the criteria was, all values would be based on it, makes sense now. All working good with the WHERE clause.