Deleting Duplicate Records

[rarubio1]

What is the SQL syntax for deleting duplicate records from a table in SQL Server.

Thanks
RR

 

[JamesLean]

Obviously it depends on your columns, what you consider to be a duplicate and how you decide which of them to keep.

eg, assume you have a table of email addresses which have subscribed to your mailing list. The table contains fields for email address and date it subscribed. You want to delete any duplicate addresses, leaving the earliest date. One way to do this is below:

CREATE TABLE Emails (
  Email varchar(50),
  SubsDate datetime
)

DELETE emails
FROM emails e
WHERE subsdate <> (
  SELECT MIN(subsdate)
  FROM emails
  WHERE email = e.email
)

--James

 

[StoneDeCroze]

I usually do the following:

SELECT DISTINCT * INTO #temp FROM Table

DELETE FROM Table

INSERT INTO Table() SELECT * FROM #temp