SQL Server Delete Duplicate Rows

There can is types of duplication of rows in a table

1. Entire row getting duplicated because there is no primary key or unique key.

2. Only primary key or unique key value is different and remaining all values are same.

Scenario 1:delete duplicate rows without primary key or unique key.

Let us create the following example.

CREATE TABLE Customers1 (CustId Int, CustName varchar (), custcity varchar (), passport_number varchar) go

Insert into Customers1 Values (1, ' John ', ' Paris ', ' p123x78 ')

Insert into Customers1 Values (2, ' Martin ', ' London ', ' l873x92 ')

Insert into Customers1 Values (3, ' Smith ', ' New York ', ' n293y99 ')

I nsert into Customers1 Values (1, ' John ', ' Paris ', ' p123x78 ') go

SELECT * FROM Customers1 go

We want remove one of the duplicate records of John.

By issuing the following summary query, we can see which see which records is duplicate.

SELECT * from Customers1 Group by Custid,custname, Custcity, Passport_number have count (*) > 1

Now we'll add this row to a local temporary table.

SELECT * into #Temp_customers1 from customers1 where 1 = 2 Insert to #Temp_customers1 Select * from Customers1 Gro Up by Custid,custname, Custcity, Passport_number have count (*) > 1

Now, the situation is, the duplicate row is in the local temporary table. All we need to are to and delete records from main table customers1 as per matching custid of the local temporary table.

Delete from Customers1 where CustID in (select CustID from #Temp_customers1)

Would the above query work? Not entirely, as by using the above query, we lost all the duplicate records!! Let us see the table again.

SELECT * from Customers1 go

Now to keep one record of John, we'll take a help of the the local temporary table again. Let us add the same record from temporary table to customers1 table.

Insert into Customers1 select * from #Temp_customers1 go

Finally we got a single record of John at the end. Let us confirm by seeing the Customers1 table.

SELECT * FROM Customers1 go

Once done, we can drop the local temporary table.

Scenario 2:delete Duplicate rows where primary key or unique key value is different and remaining values is same.

Let us create the following example.

CREATE TABLE Customers2 (CustId Int Primary Key, custname varchar, custcity varchar), Passport_number varchar (2 0)) Go

Insert into Customers2 Values (1, ' John ', ' Paris ', ' p123x78 ')

Insert into Customers2 Values (2, ' Martin ', ' London ', ' l873x92 ')

Insert into Customers2 Values (3, ' Smith ', ' New York ', ' n293y99 ')

Insert into Customers2 Values (4, ' John ', ' Paris ', ' p123x78 ')

Insert into Customers2 Values (5, ' John ', ' Paris ', ' p123x78 ')

SELECT * FROM Customers2 go

Here is the same customer's record, but this time John's record had been added thrice with different customer IDs but same Passport number!

Scenario 2.a:delete Duplicate rows but keep one using CTE

We need to use the technique of self Join initially to check for duplicate records containing different custid but same PA Ssport number.

SELECT distinct a.* from Customers2 a joins Customers2 b on A.custid <> b.custid and a.custname = B.custname an D a.custcity = b.custcity and A.passport_number = B.passport_number

Now we had realized that CustID 1, 4 & 5 is duplicate. The self-join statement accompanied by DELETE statement would give us the desired output of keeping the last duplicate Reco Rd by eliminating all the previous duplicate records. We'll use the Common Table Expression (CTE) and put the ' self ' Join query in it.

With duplicates as (select distinct A.custid as customer_id from Customers2 a join customers2 B on A.custid <> b . CustID and A.custname = b.custname and a.custcity = b.custcity and A.passport_number = b.passport_number) Delete fro M Customers2 where CustID in (select customer_id from Duplicates) and CustID <> (select Max (customer_id) from Duplic Ates)

Let ' s check which rows got deleted.

SELECT * from Customers2 go

Scenario 2.b:delete all duplicate records but keep the first original one

Let ' s first truncate the CUSTOMERS2 table and add the same rows again.

Truncate Table customers2 Go

Insert into Customers2 Values (1, ' John ', ' Paris ', ' p123x78 ')

Insert into Customers2 Values (2, ' Martin ', ' London ', ' l873x92 ')

Insert into Customers2 Values (3, ' Smith ', ' New York ', ' n293y99 ')

Insert into Customers2 Values (4, ' John ', ' Paris ', ' p123x78 ')

Insert into Customers2 Values (5, ' John ', ' Paris ', ' p123x78 ') go

The only change in the sub query would be, the we need to use min (CustomerID) instead of Max (CustomerID).

So the query would be as follows.

With duplicates as (select distinct A.custid as customer_id from Customers2 a join customers2 B on A.custid <> b . CustID and A.custname = b.custname and a.custcity = b.custcity and A.passport_number = b.passport_number) Delete fro M Customers2 where CustID in (select customer_id from Duplicates) and CustID <> (select min (customer_id) from Duplic Ates)

Let us confirm the customers2 table.

SELECT * from Customers2 go

And that's how we can delete duplicate records in SQL Server with tables without primary key, containing primary key and B Y keeping one original row.

Original link: http://www.codesec.net/view/449563.html

SQL Server Delete Duplicate Rows