Re: SQL Update with aggregate MIN function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <ciaranj@xxxxxxxxxxxxxxxxxxx> wrote in message
news:uFt9gb10FHA.3300@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
> "Vincent Johns" <vjohns@xxxxxxxxxxxxxxxxxx> wrote in message
> news:qQE4f.1733$fc7.1265@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> John Spencer wrote:
>>
>>> 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 <vjohns@xxxxxxxxxxxxxxxxxx>
>> Please feel free to quote anything I say here.
>>
>
>


.

Relevant Pages

  • Re: Performance: Query optimizer producing sub-optimal result?
    ... Have you tried adding the query hint OPTION to your query ... Mark Ayliffe wrote: ... > SELECT UID, fnCheck ... > once per record in MyTable, not once per DISTINCT MainPart as I'd expected. ...
    (microsoft.public.sqlserver.programming)
  • Re: Access 2007
    ... Arnold ... "Mark A. Sam" wrote: ... Now I am crashing every time I open a table or query in ...
    (microsoft.public.access.tablesdbdesign)
  • Re: long running queries
    ... Mark is right -- you don't have much of an option with SQL Server 2000. ... >> that would result in a large data set and complex query, ... > Some of the client tools will warn you if the query looks like its going ...
    (microsoft.public.sqlserver.olap)
  • Re: lowest value
    ... In order to eliminate 0 times, inactive athletes and to include only meets within a certain date, I have written a query. ... Athlete ... >> I have a database of swimmers and times by stroke and by meet. ... > to get the lowest time by stroke and by swimmer. ...
    (microsoft.public.access.gettingstarted)
  • RE: Link a text box to combo boxes on a form.
    ... "mark" wrote: ... properties window) to a query called "Query from FCR Form" <without quotes of ... The textbox in the form needs to display values from a query titled Shell & ... The table involved in the Project Estimate is a table titled Raw Data ...
    (microsoft.public.access.forms)