CODEWITHSUNDEEP
sqlms-accessms-access-2010
AddyProg
AddyProg

Reputation: 3050

Finding duplicate records from table and deleting all but one with latest date

I have a table structure like given below

FileId      FileName          CreatedBy       CreationDate

-------    ----------        -----------      ------------
File1_1    File1                User1        2014/09/12 17:10:49
File1_2    File1                User1        2014/09/11 17:45:49
File2_1    File2                User2        2014/05/11 17:45:22
File2_3    File2                User1        2014/08/11 17:47:22

I want to write a query that will get files with their names who have duplicated entries and delete all entries but the one with latest date.. For example in current example it will delete the record with id File1_2 and File2_1

Upvotes: 2

Views: 110

Answers (2)

HansUp
HansUp

Reputation: 97131

Start with a SELECT query which identifies the rows you want deleted.

SELECT y.CreatedBy, y.FileId, y.FileName, y.CreationDate
FROM YourTable AS y
WHERE
    y.CreationDate <  
        DMax(
            "CreationDate",
            "YourTable",
            "FileName='" & y.FileName & "'"
            );

After you verify that query identifies the correct rows, convert it to a DELETE query.

DELETE
FROM YourTable AS y
WHERE
    y.CreationDate <  
        DMax(
            "CreationDate",
            "YourTable",
            "FileName='" & y.FileName & "'"
            );

Upvotes: 2

radar
radar

Reputation: 13425

DELETE TableA 
FROM TableA A
INNER JOIN (
SELECT MAX(creationDate) as dt, FileId FROM TableA
GROUP by FileId
HAVING count(*) > 1
) T
where A.FileId = T.FileId
and A.dt < T.dt

Upvotes: 0

Related Questions