Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Tue, 02-Sep-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

Delete Duplicate rows from the table.

Deysomnath
Posted by under Sql Server category on for Intermediate level | Views : 8957 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
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.
Found interesting? Add this to:


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)