0

I'm trying to update my race results table with the user's age category, which is calculated by comparing age and gender with an age range in another table, something like this ..

update race_results.race_category = category.categoryname
where age at time of event
is < category.agemin 
and age at time of event > category.agemax 
and category.gender = user.gender

select datediff (yy, raceresults.race_date, user.dob)

It needs to update about 4500 records

But I can't get the age at time of event into the sql.

user table 
user.id, user.dob, user.gender

raceresults table
raceresults.id, raceresults.race_date, race_results.race_category

category table
agemin, agemax, gender, categoryname

Thanks.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
csbchris
  • 23
  • 2
  • 1
    It appears to me that the piece of the puzzle you're looking for, and that's critical, is missing from the schema. If you don't have the event time, how are you expecting to calculate age from dob if it's to some time in the past, and not the current time? – Jaaz Cole Jun 16 '14 at 23:15
  • Thanks. I can calculate the at at time of event can by comparing raceresults.date and user.dob as follows "select datediff (yy, raceresults.race_date, user.dob)", but I can't work out how to include that into the query. – csbchris Jun 17 '14 at 07:19

1 Answers1

0

I think there is some query error. Try to use this query:-

UPDATE race_results
SET race_category = (SELECT categoryname FROM  category)
WHERE datediff (yy, raceresults.race_date, user.dob) < category.agemin 
AND datediff (yy, raceresults.race_date, user.dob) > category.agemax 
AND category.gender = user.gender
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • Thanks. The age at at time of event can be calculated by the "select datediff (yy, raceresults.race_date, user.dob)" but I can't get that into the query – csbchris Jun 17 '14 at 07:17
  • Almost .. but I get "could not be bound" error messages on user.dob & user.gender and category,agemin, agemax & gender – csbchris Jun 17 '14 at 13:04
  • category table isn't joined, I'm just trying to select from it and update race_results table. – csbchris Jun 17 '14 at 13:05