Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Sureshkumar... » Deleting Duplicate Records Made Easy in 2005
Add to briefcase

Deleting Duplicate Records Made Easy in 2005 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
learn.rakish
Posted Wednesday, November 19, 2008 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 02, 2008 10:02 AM
Points: 1, Visits: 5

With ROW_NUMBER ( ) Function And using CTE the Duplicate Records Can be Eliminated Easily.

Suppose we have the situation to delete some duplicate records in our table. Suppose consider one table

create table #Test
(
EmpID int,
EmpName varchar(50)
)

–Insert the Records into #Test table

insert into #Test values(1,’Daya‘)
insert into #Test values(1,’Daya‘)
insert into #Test values(1,’Daya‘)

Now i have two duplicate records inserted and i want to delete those records. The following query will delete the duplicate records

–Query to Delete Duplicate Records

WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
Post #604976
Jerry Hung
Posted Wednesday, November 19, 2008 8:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:02 PM
Points: 768, Visits: 1,159
Note: watch out for ending previous statement with SEMICOLON

Here's a working code sample
CREATE TABLE #Test
(
EmpID INT,
EmpName VARCHAR(50)
)

INSERT INTO #Test VALUES(1,'Daya')
INSERT INTO #Test VALUES(1,'Daya')
INSERT INTO #Test VALUES(1,'Daya')

SELECT * FROM #Test;

WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
--SELECT FROM Emp WHERE RNUM > 1

SELECT * FROM #Test

DROP TABLE #Test

SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #605198
weblorquins
Posted Monday, July 16, 2012 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 16, 2012 5:50 AM
Points: 2, Visits: 0
Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

For more Interview question Click on billow link.

http://sqlserver4us.blogspot.in/
Post #1330049
rhythmk
Posted Monday, July 16, 2012 6:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603
weblorquins (7/16/2012)
Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

For more Interview question Click on billow link.

http://sqlserver4us.blogspot.in/


But this method will not be applicable if there is no id or numeric column.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1330059
bitbucket-25253
Posted Monday, July 16, 2012 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 5,099, Visits: 20,191
rhythmk (7/16/2012)
weblorquins (7/16/2012)
Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

For more Interview question Click on billow link.

http://sqlserver4us.blogspot.in/


But this method will not be applicable if there is no id or numeric column.


It will work ... for example
create table #Test
(EmpID VARCHAR(8),
EmpName varchar(50))
insert into #Test VALUES('abc','Daya')
insert into #Test values('abc','Daya')
insert into #Test values('abc','Daya')

;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
select EmpId, EmpName FROM #Test
Result:
abc	Daya

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1330140
rhythmk
Posted Monday, July 16, 2012 10:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603
bitbucket-25253 (7/16/2012)
rhythmk (7/16/2012)
weblorquins (7/16/2012)
Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

For more Interview question Click on billow link.

http://sqlserver4us.blogspot.in/


But this method will not be applicable if there is no id or numeric column.


It will work ... for example
create table #Test
(EmpID VARCHAR(8),
EmpName varchar(50))
insert into #Test VALUES('abc','Daya')
insert into #Test values('abc','Daya')
insert into #Test values('abc','Daya')

;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
select EmpId, EmpName FROM #Test
Result:
abc	Daya



Hi bitbucket,

I indicated the method posted by "weblorquins", the one used with table ID column and Group By clause.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1330491
kirthiga.s
Posted Monday, July 16, 2012 11:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, February 11, 2013 2:46 AM
Points: 903, Visits: 41
It is easy to delete records by considering two colmns
create table #Test
(EmpID VARCHAR(8),
EmpName varchar(50))
insert into #Test VALUES('abc','Daya')
insert into #Test values('abc','Daya')
insert into #Test values('def','Daya')

;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
select * FROM #Test

Output
EmpID	EmpName
abc	Daya
def	Daya
Post #1330494
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.