I have a database with duplicate customer records. However the only fields that duplicate are the first and last name fields and the id of the user connected to them. Not their ids. I have created a table with the duplicates. The problem is I have no idea how to write the statement to delete them. I have say three John Smiths, three different ids. The table I need to delete him from is based on id. I need to delete his first and second record but not the third. Some people repeat twice, some up to five times. Can I pull out a list of distinct names with ids to keep? Is it even possible to write a statement to delete the right records or is the only fix a manual one?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
is delete impossible?
- Thread starter krisc13
- Start date
Are all the entries unique names or might you have more than 1 person with the same name.
If there are no duplicate names, select distinct will pull out only 1 occurence of each name. If the id's are related to other tables, some records in those could become orphaned.
How many records are in the table?
Sometimes it is faster to fix it manually rather than spend ages creating a clever solution.
Keith
If there are no duplicate names, select distinct will pull out only 1 occurence of each name. If the id's are related to other tables, some records in those could become orphaned.
How many records are in the table?
Sometimes it is faster to fix it manually rather than spend ages creating a clever solution.
Keith
- Thread starter
- #3
Well see the table I want to delete them from is all ids. I've determined which customers repeat based on the user_id connected to them. If John Smith is with 192 twice, it's a duplicate. But John Smith's ids are 123 and 456. In my table I have 123 with 192 and 456 with 192. I want one to go away. And I know you're right it might just be a manual fix but there are about 90k duplicates. I was hoping to come up with something.
Not sure I understand the structure/relationships of your tables.
How are new records created?
Do the Id's you want to erase refer to anything?
How did the multiple instances occur, same person re-registering or prog. glitch?
Keith
How are new records created?
Do the Id's you want to erase refer to anything?
How did the multiple instances occur, same person re-registering or prog. glitch?
Keith
- Thread starter
- #5
It was a glitch when we migrated data from another database. Some customers were entered more than once. Some 4 and 5 times actually. But this is 90k out of 500k so I'd rather not do the whole thing over. I have a customer table with customer id and names. I have another table that connects the customer to their agent. Say John Smith is in twice. In the customer user table his id connects to his agent's id. So he has two ids in the customer user table when I need only one. I want to delete any ids from the customer user table that belong to John Smith except one.
Now I understand
Rather than delete records, it would be easier creating a new table with distinct values but, as mentioned earlier, beware of any genuine duplicate names.
I assume that the duplicate names have been given the same agent number.
Keith
Rather than delete records, it would be easier creating a new table with distinct values but, as mentioned earlier, beware of any genuine duplicate names.
I assume that the duplicate names have been given the same agent number.
Keith
- Status
