SQL Update with aggregate MIN function

[cjmccann]

Hi


I'm having a problem updating a table using the UPDATE command and I'm going
crazy..!

I have a results table with the following fields (among others)

athlete,training_sessions, result, lowest_result

Many athletes have many training_sessions (M:M)

One Athlete can have n results for the same training_session.

I want to find the their lowest result and set the lowest_result field with
a tick (yes/no field).

Thus far I've tried the following;

UPDATE results
SET lowest_result=true
WHERE result = (SELECT MIN(result) FROM results);

It updates only one record.

How do I get it to take the athlete and training_session into account.

thanks in advance.
b rgds
CJM

 

[giorgio rancati]

Hi,

If I understood

----
UPDATE results
SET lowest_result=true
WHERE result = (SELECT MIN(result)
FROM results r2
WHERE r2.training_sessions=results.training_sessions);

 

[cjmccann]

Hi Giorgio,

That is working better, though I have a slight problem in that it is
updating all the records. Let me explain.

for example, Results table

athlete, training_sessions, result, lowest_result

Ben weights1 80 yes/no

Ben weights1 79 yes/no

Joe weights2 60 yes/no

Joe weights2 58 yes/no

I would like records 2 and 4 to be updated (set to true/yes).

Can we refine this?

Thanks for your help.

Ciaran

 

[John Spencer]

I would try the following. TEST IT FIRST on a copy of your data.
UPDATE results
SET lowest_result=true
WHERE result =
(SELECT MIN(R.result) FROM results As R
WHERE R.Athlete = Results.Athlete AND
R.TrainingSession = Results.TrainingSession)


One problem is that if the lowest result is a tie, you will get more than one
record marked as the lowest result for that athlete + training session combination

 

[Vincent Johns]

I would try the following. TEST IT FIRST on a copy of your data.
UPDATE results
SET lowest_result=true
WHERE result =
(SELECT MIN(R.result) FROM results As R
WHERE R.Athlete = Results.Athlete AND
R.TrainingSession = Results.TrainingSession)


One problem is that if the lowest result is a tie, you will get more than one
record marked as the lowest result for that athlete + training session combination

Another problem with storing this kind of information into a Table is
that it may become invalid when some other record is updated, deleted,
or added. Getting this information from a Query instead of from a field
in the Table would give you fairly current information.

OTOH, if you rarely change the contents of records in the Table but
frequently read the Table, then updating it as you are planning to do
makes sense, but you'll probably have to re-run your Update Query before
you read anything from the Table, if any record in it has been changed
since the previous time you updated the field. It can be a pain to have
to keep track of that.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[cjmccann]

Hi Vincent,



I hope you don't mind another question... Thank you all the same.



The suggested solution(s) is/are working better, though I have a slight
problem in that it is

updating all the records. Let me explain.



for example, Results table



athlete, training_sessions, result, lowest_result



Ben weights1 80 yes/no



Ben weights1 79 yes/no



Joe weights2 60 yes/no



Joe weights2 58 yes/no



I would like records 2 and 4 to be updated (set to true/yes).



Can we refine this? Also, as per a point raised to have to maintain this in
the same table .v. a query, this setup is quite central to how I pull other
pieces of information together. (the amount of data presently is quite small
and therefore manageable, manually). So, I plan to update the table as the
new data entered is 'posted' (via a button) on a form. This ensures that
the underlying table is always up-to-date.



Thanks in advance for your help.



Ciaran

 

[John Spencer]

IF you are going to run this and want ONLY the specific records marked then
you need to do TWO queries; the first to clear any currently marked items
and the second to find and mark just the items you want marked.

First, clear all the marked records
UPDATE Results
Set Lowest_Result = False
WHERE Lowest_Result = True

Now mark just the lowest
UPDATE results
SET lowest_result=true
WHERE result =
(SELECT MIN(R.result) FROM results As R
WHERE R.Athlete = Results.Athlete AND
R.TrainingSession = Results.TrainingSession)

If you were trying to restrict this to just one athlete and session you
could use a where clause to limit the records further.
UPDATE Results
Set Lowest_Result = False
WHERE Lowest_Result = True
AND Athlete = "Ben"
AND TrainingSession = "Weights1"

Now mark just the lowest
UPDATE results
SET lowest_result=true
WHERE Athlete = "Ben"
AND TrainingSession = "Weights1"
result = (SELECT MIN(R.result) FROM results As R
WHERE R.Athlete = "BEN"AND
R.TrainingSession = "Weights1")

 

[cjmccann]

Hello John,

this is great...it works! So, when exiting the form (as per your
suggestion) I have a query to clear current lowest and another to find /set
the lowest.

Thanks for your help.

Ciaran

 

[cjmccann]

Sorry, one last thing - I have a date field which (as previously mentioned
with 2 lowest results the same) I would like to get the LAST (date).

How does this work in putting 2 aggregate functions within the one
subselect?

I can see the finishing line!!!

Thank you.
Ciaran

 

[John Spencer]

UNTESTED SQL STATEMENT FOLLOWS:
No guarantees that this will work at all, let alone give you the correct results.

UPDATE results
SET lowest_result=true
WHERE TheDateField =
(SELECT Max(R1.TheDateField)
FROM Results AS R1
WHERE R1.Athlete = Results.Athlete AND
R1.TrainingSession = Results.TrainingSession AND
R1.Result =
(SELECT MIN(R.result) FROM results As R
WHERE R.Athlete = Results.Athlete AND
R.TrainingSession = Results.TrainingSession))

 

[cjmccann]

Hi John,

that seems to have done the trick - many thanks, I wouldn't have been able
to get there without your help.

b rgds,
Ciaran