CODEWITHSUNDEEP
sqlsql-serversql-server-2008
neojakey
neojakey

Reputation: 1663

Finding duplicate entries SQL

I have a table of called Member(Unique ID is MemberID) that has numerous member duplicates with only the First and Last Names being different, but the Business Name, Address, City, State and Zip Codes are all the same. Records were imported with duplicates.

How can I run a script to find duplicate members where the BusinessName, Addr1, City, State, and ZIP are all the same.

I want to list them all on a page, so I can choose which ones to eliminate.

Any ideas how I can create a script for this?

Many thanks in advance,

Paul

Upvotes: 3

Views: 372

Answers (2)

Kyra
Kyra

Reputation: 5407

select * from Member as m
where exists(select MemberID 
      from Member as m2 
      where 
           (m.BusinessName = m2.BusinessName or (m.BusinessName is null and m2.BusinessName is null)) and 
           (m.Addr1 = m2.Addr1 or (m.Addr1 is null and m2.Addr1 is null)) and 
           (m.City = m2.City or (m.City is null and m2.City is null)) and 
           (m.State = m2.State or (m.State  is null and m2.State is null)) and 
           (m.ZIP = m2.ZIP or (m.ZIP  is null and m2.ZIP is null)) and 
           m.memberID <> m2.MemberID)

With the above query the where is checking to see if a duplicate entry exists. The subquery returns a result only if there is a copy where the MemberID does not match. This means if there is a unique row then there will be no results whereas if there is a row with one or more copies then it will be returned.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You want to use an analytic function for this:

select m.*
from (select m.*,
             count(*) over (partition by BusinessName, Address, City, State, ZipCode) as NumDups 
      from members m
     ) m
where NumDups > 1

NumDups tells you how many duplicates there are.

Upvotes: 1

Related Questions