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>)

SSMS is just an IDE-like application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? — Thom A, Mar 06 '23 at 14:46

score 1 · Accepted Answer · answered Mar 06 '23 at 14:49

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.

score 0 · Answer 2 · answered Mar 06 '23 at 14:50

If I am reading between the lines correctly, you want to UPDATE anything apart from the "top 1 row of each group" where the value for IsCurrentAddress is 'Y' and set it to 'N'. If that's the case,one solution is an UPDATEable CTE:

WITH CTE AS(
    SELECT IsCurrentAddress,
           ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY CreatedDateTime DESC) AS RN
    FROM dbo.YourTable)
UPDATE CTE
SET IsCurrentAddress = 'N'
WHERE IsCurrentAddress = 'Y'
  AND RN > 1;

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

2 Answers2