Finding the earliest datetime entry, and then updating the database on these reults

[colinw]

I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example:

My Fields in the table are as follows.
Log_ID, Cust_Ref_No, ActionDateTime, Action_Code

The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e.

12345,ABCDEF,01/01/2007 00:00:01.000,6
12346,ABCDEF,01/01/2007 09:00:00.000,6
12347,ABCDEF,01/01/2007 17:00:00.000,2

In the above I need to change the first record Action_Code from a 6 to a 1, but leave all other records unaffected.

All help greatly appreciated, Thanks : o )

[colinw]

This should do it:

UPDATE yourtable
SET Action_Code = 1
FROM yourtable
INNER JOIN (SELECT Log_ID, min(ActionDateTime) AS ActionDateTime
FROM yourtable
GROUP BY Log_ID) a ON yourtable.Log_ID = a.Log_ID AND yourtable.ActionDateTime = a.ActionDateTime
WHERE yourtable.Action_Code = 6