EDUDOTNET

Tuesday, October 22, 2013

How to find duplicate records in a table of SQL server database using a SQL query?

We have a Customer table in sql server database with multiple fields and also data. Lots of data in this table and also multiple duplicates records of customers in this table.

We want to find all duplicates records of customers and based on "CustCode" column. And also need to delete all duplicates records from Customer table. A customer should be only one record in this table.

We have following Customer table with data:

CustID
CustName
CustCode
Address
Country
PinCode
101
John
EDU005
#Ence Cot
Norway
0NY001
102
Golfy
EDU006
#30 Dam
Estonia
XPOUTR
103
Thom
EDU007
Barkin 80
IceLand
554211
104
John
EDU005
#Ence Cot
Norway
0NY001
105
Dolly
EDU009
#30 MarKha
Lithaunia
PQRSTK
106
Thom
EDU007
Barkin 80
IceLand
554211
107
John
EDU005
#Ence Cot
Norway
0NY001
108
Golfy
EDU006
#30 Dam
Estonia
XPOUTR
109
Thom
EDU007
Barkin 80
IceLand
554211

We can able to find out all duplicates records and delete duplicates records using following sql query:

WITH Cust AS (SELECT  *, rownumber = ROW_NUMBER()
            OVER(PARTITION BY CustCode ORDER BY CustCode)
            FROM Customer)

DELETE Cust WHERE rownumber > 1

After run this query we deleted all duplicated records from Customer. Remaining only one record of per customer. See below Customer table with data:

CustID
CustName
CustCode
Address
Country
PinCode
101
John
EDU005
#Ence Cot
Norway
0NY001
102
Golfy
EDU006
#30 Dam
Estonia
XPOUTR
103
Thom
EDU007
Barkin 80
IceLand
554211
105
Dolly
EDU009
#30 MarKha
Lithaunia
PQRSTK








0 comments:

Post a Comment