Delete Duplicate rows from the table.

Posted by Somnath Dey in Sql Server category on 1/28/2008 for Intermediate level | Views : 12612

Rating: 4 out of 5

1 vote(s)

Post Article |

Search Articles |

Articles Home

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

About the Author

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.

Bookmark It

Login to vote for this post.

Latest Articles

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

Comment using

(Author doesn't get notification)