Updating multiple records with conditions

davidevan · #1 (**permalink**) 11-10-06, 18:39

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(SQLUpdate)
	
   ' move to next record to update....
    rstRecSet.MoveNext
    	
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.

davidevan · #2 (**permalink**) 11-11-06, 00:27

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech