Duplicate records and updating a field

[GPowers]

I am trying to update a field when I have duplicate records. However, I do not want to update the most recent record. I have a date field that I have sorted by so the oldest duplicates would be listed first.

Serial_No Tag_No Date U_Delete
bcertu as12345 20020102
bcertu as12345 20030105
bcertu as12345 20030324

In this situation I want to flag the first two records as "D", since they are the oldest records of this duplicate.

Thanks for any assistance

 

[padinka]

I think the easiest thing would be to mark the records that you don't want to update. Here's some sample code

UPDATE MyTable AS a
SET a.U_Delete = "Keep"
WHERE a.MyDate=(select max(MyDate) from MyTable where Serial_No = a.Serial_No );
Trisha
padinka@yahoo.com

 

[padinka]

Oh yeah, just a reminder to back up your data!
Trisha
padinka@yahoo.com