Solved Update a field in a table based on the result of a subquery

[GoodyGoody]

Hi, I am trying to run some SQL to update a field in one table with the result of a nested subquery which returns just one field. The code is below:

UPDATE Runners SET Runners.Category = (select RunnerCategory.RunnerCategoryID from RunnerCategory
WHERE [RunnerCategory].[RunnerCategoryGender]=runners.Gender AND Runners.Age >=runnercategory.[RunnerAgeFrom] AND Runners.Age <=runnercategory.[RunnerAgeTo]);

Access gives the error "Operation must use an updateable query".

Can anyone help please?
Thanks in advance

 

[theDBguy]

Hi. Just a wild guess but maybe try something like.

UPDATE Runners R
INNER JOIN RunnerCategory RC
ON R.Gender=RC.RunnerCategoryGender
AND R.Age>=RC.RunnerAgeFrom
AND R.Age<=RC.RunnerAgeTo
SET R.Category=RC.RunnerCategoryID

However, it's considered against normalization rules to store redundant data in multiple tables when you can just look it up.

 

[isladogs]

Agree with the comments above about not duplicating data.
I would also avoid using a subquery. However you may also need to specify unique records.
Try UPDATE DISTINCTROW .....

 

[arnelgp]

UPDATE Runners
INNER JOIN
RunnerCategory
ON
Runners.Gender = RunnerCategory.RunnerCategoryGender
SET Runners.Category = [RunnerCategory].[CategoryID]
WHERE (((Runners.Age) Between [RunnerCategory].[RunnerAgeFrom] And
[RunnerCategory].[RunnerAgeTo]));

 

[GoodyGoody]

Hi. Just a wild guess but maybe try something like.

UPDATE Runners R
INNER JOIN RunnerCategory RC
ON R.Gender=RC.RunnerCategoryGender
AND R.Age>=RC.RunnerAgeFrom
AND R.Age<=RC.RunnerAgeTo
SET R.Category=RC.RunnerCategoryID

However, it's considered against normalization rules to store redundant data in multiple tables when you can just look it up.

Thank you. Yes in terms of data people are often happy to give their Age but not their DOB so the DB allows both. This exercise is an initial exercise to set the data for an upcoming series of races.

 

[GoodyGoody]

Th

Agree with the comments above about not duplicating data.
I would also avoid using a subquery. However you may also need to specify unique records.
Try UPDATE DISTINCTROW .....

Thank you