Deleting Duplicate Records Made Easy in 2005

Question

Post reply

Deleting Duplicate Records Made Easy in 2005

learn.rakish

Grasshopper

Points: 11
More actions
November 19, 2008 at 3:01 am

#196167

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

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')

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

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

weblorquins SSC Rookie Points: 26 More actions · Answer 2

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/[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

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/[/url]

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

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

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/[/url]
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')

;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:

abcDaya

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

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

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/[/url]
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:
abcDaya

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

kirthiga.s SSC Eights! Points: 917 More actions · Answer 6

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

EmpIDEmpName

abcDaya

defDaya