-
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;
-
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.
-
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
-
Forum Rules
|
|