The most effective way to update a table with a newest record from another table

advertisements

I am trying to run a simple update statement that takes data from one table and inserts into another. The problem is that the table where the data resides may have one or more records so I want to use the most recent record that contains data.

Here is my code and mind you this code works just fine initially. My worry though is if I have a situation where there are multiple records, I want to update the most recent record's data. The table with the data does have a DateTimeEntered field to utilize:

Update C
Set C.CaseNumber = L.Docket_NO
From dbo.Cases as C
Inner Join dbo.messages as L
on L.AccountID = C.AccountID
where (L.Docket_NO <> '' and L.Docket_NO != C.CaseNumber)
or (L.Docket_NO <> '' and C.CaseNumber is Null)

So If I try the CTE method I would do something like this:

With CTE_Messages as (Select AccountID, DOCKET_NO, DateTimeEntered from dbo.messages where DateTimeEntered = (Select MAX(DateTimeEntered) from dbo.messages))

Update C
Set C.CaseNumber = L.Docket_NO
From dbo.Cases as C
Inner Join CTE_Messages as L
on L.AccountID = C.AccountID
where (L.Docket_NO <> '' and L.Docket_NO != C.CaseNumber)
or (L.Docket_NO <> '' and C.CaseNumber is Null)

Do I need to insert the where and/or clause into the CTE as well to get the pertinent data? Am I thinking too hard on this where the CTE isn't needed and I just need to put a subquery on the Set and Find the Max datetimeentered there?

I am really just looking for the most efficient way of accomplishing this. Can anyone help this SQL neophyte? Any insight would be most appreciated.

Edit: Thank you for the answers. But here is my main problem.

I originally used something like this:

  Update C
  Set C.CaseNumber = L.Docket_NO
  From dbo.CourtCases as C
  Inner Join dbo.messages as L
   on L.AccountID = C.AccountID
  where (L.Docket_NO <> '' and L.Docket_NO != C.CaseNumber)
  or (L.Docket_NO <> '' and C.CaseNumber is Null)

This simple update statement worked until I realized that I don't know what record SQL is pulling from dbo.messages to update CourtCases.CaseNumber. Dbo.Messages is somewhat of a historical table and there could potentially be multiple records. Some of these records will be different or old and they need to be updated. I originally latched onto Datetimeentered from dbo.messages to give me the most recent item but the problem is that not every record in dbo.messages contains a Docket_NO to use.


If I understand this correctly, you can add another where ('and' in this case) to choose the most recent record:

Update C
Set C.CaseNumber = L.Docket_NO
From dbo.Cases as C
Inner Join dbo.messages as L
on L.AccountID = C.AccountID
where (L.Docket_NO <> '' and L.Docket_NO != C.CaseNumber)
or (L.Docket_NO <> '' and C.CaseNumber is Null)
**and c.casenumber = (Select c.casenumber from cases where datetimeentered =
(select max(datetimeentered) from cases)**