Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting Duplicate Records Made Easy in 2005 Expand / Collapse
Author
Message
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 2, 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
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: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
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
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
Posted Monday, July 16, 2012 6:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 559, Visits: 884
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
Posted Monday, July 16, 2012 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,572, Visits: 24,796
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
Posted Monday, July 16, 2012 10:42 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 559, Visits: 884
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
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