dcsimg
Results 1 to 3 of 3

Thread: Deleting duplicate records (but keeping one original)

  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Deleting duplicate records (but keeping one original)

    I have a table that has 1335 records in, and columns names, Title, First Name, Surname, Address, Address2, Town/City, County, Postcode.

    I want to know how to write a SQL code, which will locate all the duplicates that match the First Name, Surname and Postcode and delete them, but keeping 1 copy of the duplicate.

    I have created a query to locate the duplicate records, and I have put the SQL code there. I tried changing this, however it would delete all the records, so I am not to sure what to try...

    SELECT SurnameSearch.Title, SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Address, SurnameSearch.Address2, SurnameSearch.[Town/City], SurnameSearch.County, SurnameSearch.Postcode
    FROM SurnameSearch
    WHERE (((SurnameSearch.[First Name]) In (SELECT [First Name] FROM [SurnameSearch] As Tmp GROUP BY [First Name],[Surname],[Postcode] HAVING Count(*)>1 And [Surname] = [SurnameSearch].[Surname] And [Postcode] = [SurnameSearch].[Postcode])))
    ORDER BY SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Postcode;

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,912
    One way to do it is to create a temporary table with records you want to keep and writing a delete statement to delete everything in the original table and not in keep table.

  3. #3
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    1. Create a temporary table
    2. Set the primary Key to First Name, Surname and Postcode
    3. Create an APPEND query to append the original table to the temporary table.
    4. Run the query, a message will be displayed after the query runs saying " X number of records could not be appended due to record violations" or similar wording select YES and your temporary table should now contain no duplicate records.
    5. I suggest test the query using sample data to ensure that only 1 copy of the duplicate records is in the temporary table
    Allan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •