Check for Duplicate Data

[manguilla]

Hello All. I am having a problem sorting duplicate data within a table. I have a table called Employee that contains EmployeeID, EmployeeName, PhoneNumber. This table contains duplicate data. I now have a problem with 2 different users with the same EmployeeID 'abc'. I wondered if there is a way to write a select statment to delete one of the users with same id. I have tried doing a few things with DISTINCT but I could not get the EmployeeID and 2 EmployeeNames to filter out. Any help will do. Thank you very much in advance.

Manguilla

 

[ingresman]

You have to specify enough detail in the where clause to identify the record to delete. Where clauses do not have to be supported by a key/index.
If the records are identical it doesn't matter which you delete.

 

[manguilla]

Ah yes your are correct. Thanks. I have another quick question. Is there a good way to check the table for this duplicate data with multiple users with the same id before populating another table with this inconsistent data? I tried to do something like this but it's not specifying the correct ID and Name

SELECT DISTINCT TempWorkOrderFile.[Inst ID], TempWorkOrderFile.Installer
FROM TempWorkOrderFile;


Here are the results:

abc John Smith
def Peter Sampson
xyz Bob Anderson
xyz Bob Andersen


I feel I need to do a check on the ID and Name incase the xyz person comes up twice and delete the Bob Andersen instance. Is that possible with a sql statement? Thanks again.

Manguilla

 

[ingresman]

I suggested using excell to do this is a different post, do you not want to do it that way ?

 

[manguilla]

Yea I wanted to see if there was a way to do this in SQL. Thanks.

Manguilla