Home > Articles > Sql Server >

 Delete Duplicate rows from the table.

Deysomnath
Posted by under Sql Server category on for Intermediate level | Views : 9377 red flag
Delete Duplicate rows from the table.
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
Page copy protected against web site content infringement by Copyscape
About the Author
Deysomnath
Full Name: Somnath Dey
Member Level:
Member Status: Member
Member Since: 4/29/2008 9:36:46 PM
Country: United States

http://somnathdey.blogspot.com/
As soon as the fear approaches near, attack and destroy it.
Login to vote for this post.


Comments or Responses

Posted by: Sirish79 on: 4/13/2009
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

Login to post response

Comment using Facebook(Author doesn't get notification)