How to update the newest of several records

microsoft.public.sqlserver.programming

21 Dec 2005 7:42 PM

Terry Carnes

I'm writing a stored procedure that updates a record in a table which is
keyed by [ID_No], [Sub_ID] and [TimeStart].

I can easily find the record to update by finding the Top 1 Where [ID_No]
and [Sub_ID] are the IDs I want and I Order By [TimeStart] Desc.

I am wondering if there is a more concise way to update this record than
what I have come up with:

UPDATE control_table
    SET [update_me] = @new_value
    WHERE
         [ID_No]      = @ID_No AND
         [Sub_ID]     = @Sub_ID AND
         [TimeStart] = (Select Top 1 TimeStart from control_table where
[ID_No] = @ID_No AND
                                [Sub_ID] = @Sub_ID Order By Queue_Start
DESC)

Thank you for any help that you can offer.

Terry

21 Dec 2005 8:26 PM

Gary Gibbs

If you have an identity field in your table you can use @@IDENTITY
after your update statement (select @@IDENTITY). Other than that, you
are simply looking for variations of finding the TimeStart (if it is
indeed a timestamp of when the record was entered) with the maximum
value.