1

Possible Duplicate:
How Do You Delete Duplicate Records In SQL

I have a table with columns:

FirstName
LastName
AddressLine1
AddressLine2
City
State

For each row, AddressLine1 is different from AddressLine2 value. But there are some duplicates rows, in which AddressLine1 of some records matches with AddressLine2 of some other record.

I want to get rid of such records mentioned above.

Community
  • 1
  • 1
Pawan Pillai
  • 1,955
  • 5
  • 37
  • 64
  • Have you got any ideas so far? Also, what should happen if the AddressLine1 of one record matches the AddressLine2 of the other record, but FirstName or City or State are different? – marnir Dec 13 '11 at 21:37
  • also http://stackoverflow.com/questions/841447/delete-duplicate-sql-records – JNK Dec 13 '11 at 21:37
  • is there a primary key? that will make the statement smaller... – Randy Dec 13 '11 at 21:38
  • I love that we expend so much energy correcting and editing and answering such obvious duplicates that have no effort put forth. – JNK Dec 13 '11 at 21:43
  • yet another http://stackoverflow.com/questions/4406854/efficient-query-for-finding-duplicate-records – JNK Dec 13 '11 at 21:44
  • yet another http://stackoverflow.com/questions/3385661/deleting-duplicate-records – JNK Dec 13 '11 at 21:45
  • and this one http://stackoverflow.com/questions/3716565/how-to-delete-duplicate-entry-by-one-sql – JNK Dec 13 '11 at 21:46
  • and this one http://stackoverflow.com/questions/1173963/how-to-delete-duplicate-rows-with-sql – JNK Dec 13 '11 at 21:46
  • and this one http://stackoverflow.com/questions/6114512/removing-duplicate-rows-using-delete-query – JNK Dec 13 '11 at 21:47
  • http://stackoverflow.com/questions/1131085/removing-duplicate-records – JNK Dec 13 '11 at 21:49
  • http://stackoverflow.com/questions/1772592/sql-deleting-duplicate-records-in-sql-server – JNK Dec 13 '11 at 21:50
  • and this one http://stackoverflow.com/questions/91784/how-can-i-delete-duplicate-rows-in-a-table – JNK Dec 13 '11 at 21:50
  • and this one http://stackoverflow.com/questions/1173963/how-to-delete-duplicate-rows-with-sql – JNK Dec 13 '11 at 21:52
  • and also http://stackoverflow.com/questions/162325/sql-duplicate-delete-query-over-millions-of-rows-for-performance – JNK Dec 13 '11 at 21:53

3 Answers3

2

This will get all duplicate records:

SELECT P.*
FROM table P INNER JOIN
     table S ON P.FirstName = S.FirstName
            AND P.LastName = S.LastName
WHERE P.AddressLine1 = S.AddressLine2

If your table had an ID you could write a delete to remove duplicates like this:

DELETE FROM table
WHERE Id IN (
  SELECT P.Id
  FROM table P INNER JOIN
       table S ON P.FirstName = S.FirstName
              AND P.LastName = S.LastName
  WHERE P.AddressLine1 = S.AddressLine2
)
Yuck
  • 49,664
  • 13
  • 105
  • 135
  • Having a nearly identical answer, I would point out this is better if the rows are truly duplicates. It fails if for instance family members need their addresses sanitized to the same format. – Glenn Dec 13 '11 at 21:43
  • I do not have any primary key. I just got all this data in an excel and I copied it to a SQL Server table. – Pawan Pillai Dec 14 '11 at 14:20
1

Join the table to itself

DELETE a
FROM Table a
JOIN Table b
   ON a.AddressLine1 = b.AddressLine2
msmucker0527
  • 5,164
  • 2
  • 22
  • 36
0

Swap out UNIQUE_IDENTIFIER with some ID's or names or what have you do easily identify in the future. Then you can hand delete as needed. Or modify what is below into an UPDATE or DELETE statement as needed.

SELECT
  t1.UNIQUE_IDENTIFIER,
  t2.UNIQUE_IDENTIFIER
FROM
  table t1,
  table t2
WHERE
  t1.AddressLine1 = t2.AddressLine2
Glenn
  • 156
  • 1
  • 14