Delete Duplicate rows from the table.

Posted on: 1/28/2008 6:44:41 AM by Deysomnath | Views: 4042 | Category: Sql Server | Level: Intermediate | Print Article

.NET Training Videos!
Buy online comprehensive training video pack just for $35.00 only, see what's inside it.

Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.

First way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.)
Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table).

drop table EmployeeTable

sp_rename 'Emp_Temp_Table',EmployeeTable'

Then Delete the orginal table and rename the clone table with the name of orginal table.

Second way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

Truncate table EmployeeTable

insert into EmployeeTable select * from Emp_Temp_Table

drop table Emp_Temp_Table

Third way to delete duplicate rows :

Populate the new Primary Key

Alter table EmployeeTable add NewPK int NULL
Go
Declare @intCounter int
Set @intCounter = 0
Update EmployeeTable
SET @intCounter = NewPK = @intCounter + 1

Select name,RecCount=count(*), PktoKeep = max(NewPK)
Into #dupes
From EmployeeTable
Group by name
Having count(*) > 1
Order by count(*) desc

Delete dupes except one Primary key for each dup record

Delete test
from EmployeeTable a join #dupes d
a.name
where a.NewPK not in (select PKtoKeep from #dupes)

Remove the NewPK column

ALTER TABLE test DROP COLUMN NewPK
go

drop table #dupes

If you like this article, subscribe to our

RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.

Interesting?

Responses

Posted by: Sirish79 | Posted on: 13 Apr 2009 06:09:43 AM

These are some other ways for performing the same operation.

CREATE TABLE #duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO #duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO #duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO #duplicateTest VALUES(3, 'Karen','White')
INSERT INTO #duplicateTest VALUES(1, 'Bob','Smith')

Select * from #duplicatetest

SELECT * FROM #duplicatetest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

SET ROWCOUNT 1
DELETE FROM #duplicatetest WHERE ID = 1
SET ROWCOUNT 0

Select * from #duplicatetest
DELETE TOP(1) FROM #duplicatetest WHERE ID = 1
Select * from #duplicatetest

Create Table #Emptest
(
EmpID int,
Empname varchar(20)
)

Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')

Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')

Insert into #EmpTest values('3','John')
Insert into #EmpTest values('3','John')

DELETE TOP (SELECT COUNT(*) -1 FROM #EmpTest WHERE Empname = 'Bashaerat')
FROM #EmpTest
WHERE Empname = 'Bashaerat'
Select * from #EmpTest

Delete Duplicate rows from the table.

About Somnath Dey