471,089 Members | 1,296 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

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

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 )
Mar 11 '07 #1
1 1494
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
Mar 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by James | last post: by
15 posts views Thread by Fritz Switzer | last post: by
2 posts views Thread by Rey | last post: by
5 posts views Thread by Michel Posseth [MCP] | last post: by
2 posts views Thread by justin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.