how to delete duplicate rows from table in sql server

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / k l baiju

12 Answers
23196 Views
HCL, UHG, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to delete duplicate rows from table in sql server..

Answer / victor

this can help if you want to keep only different records

create table test
(
id int,
name varchar(20)
)

insert into test VALUES(1,'test')
insert into test VALUES(2,'test')
insert into test VALUES(2,'test')
insert into test VALUES(3,'test')
insert into test VALUES(4,'test')
insert into test VALUES(5,'test')
insert into test VALUES(6,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')

select * from test order by 1

while @@rowcount != 0
begin
delete top (1) test where id in
(
select id
FROM test
GROUP BY id having count(id)>1
)
end

select * from test order by 1

-- At the end you will have only differents IDS

Is This Answer Correct ?

9 Yes

2 No

how to delete duplicate rows from table in sql server..

Answer / onlymoid

CREATE TABLE dbo.duplicateTest ------Deleting
Duplicates with same id
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

SELECT * FROM dbo.duplicateTest

SET ROWCOUNT 1
DELETE FROM dbo.duplicateTest WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.duplicateTest

Drop table dbo.duplicatetest

Is This Answer Correct ?

5 Yes

2 No

how to delete duplicate rows from table in sql server..

Answer / smitha

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1

Is This Answer Correct ?

3 Yes

1 No

how to delete duplicate rows from table in sql server..

Answer / manjeet kumar

delete from table_name where column_name='value' and rowid
not in (select max(rowid) from table_name where
column_name='value');

e.g. create table duplicate (name varchar(15), rollno number
(10));

insert into duplicate (name,rollno) values ('mkumar',2);
insert into duplicate (name,rollno) values ('mkumar',2);

delete from duplicate where name='mkumar' and rowid not in
(select max(rowid) from duplicate where name='mkumar');

Is This Answer Correct ?

3 Yes

1 No

how to delete duplicate rows from table in sql server..

Answer / debasish

while @@rowcount != 0
begin
delete top (1) test where columnname in
(
select columnname
FROM tablename
GROUP BY columnname having count(*)>1
)
end

Is This Answer Correct ?

3 Yes

2 No

how to delete duplicate rows from table in sql server..

Answer / eashwar v

IN SQL SERVER 2005, This can be easily achieved without
crating unique identifier by using CTE and ROW_NUMBER (),
the modified query for sql server 2005 goes here
***********************************************
WITH T1 AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY ID,
FNAME, LNAME ORDER BY ID ) AS RNUM FROM DUPLICATE )
DELETE FROM T1 WHERE RNUM > 1
***********************************************
To get a detail grasp on ROW_NUMBER () OVER () … Refer MSDN
http://msdn2.microsoft.com/en-us/library/ms186734.aspx for.

Is This Answer Correct ?

1 Yes

0 No

how to delete duplicate rows from table in sql server..

Answer / sanjay kumar dinda

Deletion of Rows depends upon the different condition...
Consider based on n columns the entire recordset is
treating a duplicate... We can delete the duplicate by
using following method...

Add one column and set uniue values to that column..

ALTER TABLE TABLE1 ADD ID INT IDENTITY(1,1)

COnsider T contains 40 columns and based on C1,C2,C3 we
have to find the duplicate and we have to delete the same...

Delete T1

FROM TABLE1 T1,TABLE1 T2
WHETE T1.C1=T2.C1 AND
T1.C2=T2.C2 AND
T1.C3=T2.C2
AND T1.ID>T2.ID

I think this will help....

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows from table in sql server..

Answer / vignesh chandrasekaran

select Distinct column name from Table Name

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows from table in sql server..

Answer / vineet dhamija

the best approach i came across in simple form too
just create a temporary table with the distinct values and
truncate this table than copy the values back and u r good to go
select distinct * into #temp from urtable
truncate table urtable
insert into urtable select * from #temp
drop table #temp

Is This Answer Correct ?

0 Yes

0 No

how to delete duplicate rows from table in sql server..

Answer / gerry

delete from my_table where my_primary_key in
(select a.primary_key from my_table a, my_table b
where not a.my_primary_key = b.my_primary_key
and [insert restriction that makes the 2 rows the same]
)

Is This Answer Correct ?

0 Yes

3 No

Post New Answer