I would like to verify an update syntax with a cross apply.

I have a Member table which contains one or more messages in the MemberMessage table.

In the Member table, I have created a field Member.MessageAddDate which represents the MemberMessage.AddDate of the first child record created.

Now, I need to execute a SQL command that will fill that field:

UPDATE Member SET MessageAddDate=MemberMessage.AddDate
 FROM Member
 CROSS APPLY (SELECT TOP 1 MemberMessage.AddDate FROM MemberMessage
  WHERE Member.Numero=MemberMessage.NoMember ORDER BY MemberMessage.Numero) MemberMessage

This seems to be ok. I see I get 11846 records updated.

Now, I need to do the same thing with another field named Member.MessageModDate which would represent the most recent child record created:

UPDATE Member SET MessageModDate=MemberMessage.AddDate
 FROM Member
 CROSS APPLY (SELECT TOP 1 MemberMessage.AddDate FROM MemberMessage
  WHERE Member.Numero=MemberMessage.NoMember ORDER BY MemberMessage.Numero DESC) MemberMessage

As you can see, I only changed the Member replace field and adjusted the CROSS APPLY to get the most recent record.

I know in the child table I have more than one occurence per master record. However, when I execute the second command, it only replaces the same values as the first command. I would have expected this one to grab the most recent MemberMessage.AddDate for some of them, as some of them contains more than one child record, thus the MemberMessage.AddDate would be more recent.

Anyone knows what I am missing?