How could I update records that are not the most recent for a given ID?

Question

Here's an example dataset to illustrate my problem.

ID	PersonID	PersonName	Address	IsCurrentAddress	CreatedDateTime
1	20	James	123 Peach St	Y	3/1/2023
2	20	James	2844 Hollander Rd	Y	10/14/2023
3	5	Alexa	846 Anytown Way	Y	5/23/2022
4	5	Alexa	374 Wayward Blvd	Y	1/6/2023
5	5	Alexa	927 Beachnut St	Y	9/4/2023

Alexa and james moved, so the new records were created to reflect this. I want to update the older records for each person to reflect that it's no longer their current address. Essentially to detect that ID 1, 3, and 4 are not the most recent addresses for each person, and set their IsCurrentAddress to 'N'

Looking this question up elsewhere, I couldn't find any that were updating, which I think required different logic from just selecting. I was thinking something along the lines of:

UPDATE Addresses SET IsCurrentAddress = 'N'
WHERE ID IS NOT IN (<List of ID's that have the most recent CreatedDateTime for thier PersonID>)

Answer 1

We can try updating with the help of exists logic:

UPDATE Addresses a1
SET IsCurrentAddress = 'N'
WHERE EXISTS (
    SELECT 1
    FROM Addresses a2
    WHERE a2.PersonID = a1.PersonID AND
          a2.CreatedDateTime > a1.CreatedDateTime
);

The above logic sets the current address flag to no for any record for which there exists another record of the same person having a more recent creation time.