asked
How to update older "duplicate" records (duplicate except for the date column)
Greetings, We have a table that contains, for this example, links to demographic questions (questionID) for each subscriber, with a date indicating when the subscriber answered a particular demographic question. In some cases, a subscriber may have answered the same question again at a later date, and we now have multiple records for the same subscriber and questionID, but with different answer dates (see sample image): ![alt text][1] The app that was storing the answers should have flagged the older records as "deleted" (set isDeleted = 1) but it did not do so, and I now need to clean up the older records. This seems like it should be simple, but it's got me stumped. How do I (a) select any records where there are duplicate subscriberID and questionIDs but with different answer dates? And (b) how do I do an update to set all but the newest records for each subscriber to have isDeleted=1? Any help would be appreciated! I suspect a self join may be in order, but I haven't figured it out yet. Thus the question! [1]: /storage/temp/2732-sqlquestion.jpg
Yeah, absolutely a self-join with a sub-query. Test this against your data to be sure it works correctly. If it does, just modify it to an UPDATE command to get the IsDeleted column modified. SELECT * FROM dbo.Questions AS q JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY q.QuestionID, q.SubscriberID ORDER BY q.DateAnswered DESC) RN, q.QuestionID, q.SubscriberID, q.DateAnswered FROM dbo.Questions AS q ) AS q2 ON q2.QuestionID = q.QuestionID AND q2.SubscriberID = q.SubscriberID AND q2.DateAnswered = q.DateAnswered AND q2.RN > 1;
18 People are following this question.
