Results 1 to 2 of 2
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181

    Need SQL Help - Delete first/earliest duplicate

    I am trying to figure out how to delete the FIRST record in a set of duplicates. The intent is to keep the latest duplicated record, rather than the earliest.

    Right now, I am using these queries:
    Code:
    SELECT tblAmazonOrderInput.*
    FROM tblAmazonOrderInput LEFT JOIN (SELECT MIN(ID) as RowId, [Web Order], [MSKU] 
    FROM  tblAmazonOrderInput 
    GROUP BY [Web Order], [MSKU])  AS KeepRows ON tblAmazonOrderInput.ID = KeepRows.RowID
    WHERE KeepRows.RowID Is Null;
    
    DELETE tblAmazonOrderInput.*
    FROM tblAmazonOrderInput
    WHERE [ID] IN 
    (SELECT ID FROM [qryAmazonOrderInput_Dedupe]);
    Can someone help me how to revise the logic to select the first row of a duplicated set, only if there is a duplicate?

    Unfortunately, it's not as easy as (SELECT MAX(ID)) or WHERE KeepRows.RowID Is Not Null. In those cases, it selects the first record of each unique [Web Order] and [MSKU], regardless if it is duplicated or not.


    Also, I can't use DELETE * FROM tblAmazonOrderInput WHERE NOT IN (SELECT ID FROM [qryAmazonOrderInput_Dedupe]) because it would delete the first record of each unique [Web Order] and [MSKU], regardless if it is duplicated or not.

    This would be an excellent scenario to use OVER PARTITION BY, but Access doesn't allow those commands.

    Thanks for any help you can provide!!!

  2. #2
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    Solved by ordering by ID descending in parent table, and selecting MAX(ID) in KeepRows

    Code:
    SELECT tblAmazonOrderInput.* FROM tblAmazonOrderInput LEFT JOIN (SELECT MAX(ID) as RowId, [Web Order], [MSKU] 
    FROM  tblAmazonOrderInput 
    GROUP BY [Web Order], [MSKU])  AS KeepRows ON tblAmazonOrderInput.ID = KeepRows.RowID
    WHERE KeepRows.RowID Is Null
    ORDER BY tblAmazonOrderInput.ID DESC;
    
    DELETE tblAmazonOrderInput.*
    FROM tblAmazonOrderInput
    WHERE [ID] IN 
    (SELECT ID FROM [qryAmazonOrderInput_Dedupe]);

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SUm duplicate values then delete duplicate rows
    By DonKaponne in forum Queries
    Replies: 1
    Last Post: 09-14-2014, 04:18 PM
  2. Delete duplicate records
    By samdahlr in forum Access
    Replies: 8
    Last Post: 07-28-2014, 08:25 AM
  3. Delete Duplicate Query Freeze
    By Auto in forum Queries
    Replies: 18
    Last Post: 12-17-2013, 12:09 PM
  4. Delete record if duplicate
    By rubintheyid in forum Access
    Replies: 8
    Last Post: 03-30-2010, 11:33 AM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums