Solved Update a field in a table based on the result of a subquery (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 10:29
Joined
Aug 31, 2019
Messages
120
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

I’m here to help
Staff member
Local time
Today, 03:29
Joined
Oct 29, 2018
Messages
21,357
Hi. Just a wild guess but maybe try something like.
Code:
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

MVP / VIP
Local time
Today, 10:29
Joined
Jan 14, 2017
Messages
18,186
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:29
Joined
May 7, 2009
Messages
19,169
Code:
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

Registered User.
Local time
Today, 10:29
Joined
Aug 31, 2019
Messages
120
Hi. Just a wild guess but maybe try something like.
Code:
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

Registered User.
Local time
Today, 10:29
Joined
Aug 31, 2019
Messages
120
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
 

Users who are viewing this thread

Top Bottom