CODEWITHSUNDEEP
sqlsql-server-2005
RogueSpear00
RogueSpear00

Reputation: 619

Remove Duplicate Records While Retaining One Record

I have a table of phone records as such:

ID  int (Primary Key)
company varchar 
dbaname varchar 
coaddress   varchar 
cocity  varchar 
costate varchar 
cozip   varchar 
phonenum    varchar

What I want to accomplish is to remove all the duplicates phone numbers (phonenum field) but retain one occurence.

When doing a duplicate check, I see there are over 41000 duplicate phone numbers in the table (total of about 141000).

How would I go about doing this based on the phone number?

Upvotes: 0

Views: 682

Answers (1)

GarethD
GarethD

Reputation: 69789

Assuming you want to keep only the latest record:

DELETE  yourTable
FROM    yourTable T
        LEFT JOIN
        (   SELECT  MAX(ID) [ID]
            FROM    yourTable
            GROUP BY Phonenum
        ) MaxT
            ON MaxT.ID = T.ID
WHERE   MaxT.ID IS NULL

I'd definitely archive what you are deleting into another table though as there is no guarantee you are removing the correct record without checking manually or adding further criteria to the Delete statement.

Upvotes: 1

Related Questions