Aiden Aiden - 25 days ago 5
SQL Question

Delete where both columns match

I have two tables

Person (contain oldest date (farthest in future) when person moved OR WILL moved to another address)

ID OLDESTADDRESSMOVEDATE

1 20161117
2 20171211
3 20160101


Address

PersonID AddressMOVETODATE
1 20161117
1 20161111
1 20141018
2 20171211
2 20151210


Now I want to write a DELETE query which will DELETE FROM ADDRESS THE ROWS for EACH person in PERSON, where AddressMOVETODATE is PREVIOUS THAN the one in OLDESTADDRESSMOVEDATE colum

e.g.

If I run that query the remaining rows in ADDRESS table should be

PersonID AddressMOVETODATE
1 20161117
2 20171211


I have tried

DELETE FROM ADDRESS WHERE PERSONID = PERSON.ID and AddressMOVETODATE < Person.OLDESTADDRESSMOVEDATE


but it is not working. Also, how we will make sure we enter only CORRESPONDING columns of Person table e.g. we do not want to mess up by putting

Person ID = 1 and Person.OLDESTADDRESSMOVEDATE = 20171211 (of Person 2)


Please help.

Thanks,

Aiden

Answer

You can query using row_number, cte and delete

;WITH cte AS
(
    SELECT *, RowN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AddressMoveDate DESC) FROM Address
)
DELETE FROM cte WHERE RowN > 1