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?