How to retrieve duplicate rows in a table? How to delete the duplicate entries

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

How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?

Question Posted / harry

15 Answers
28391 Views
Leo Technologies, Mannar Company, Synechron, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?..

Answer / mon

DELETE FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL

Is This Answer Correct ?

0 Yes

0 No

How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?..

Answer / arun kumar k s

drop table #TEMP select distinct * into #TEMP from
TABLE_NAME delete from TABLE_NAME insert into TABLE_NAME
select * from #TEMP

Is This Answer Correct ?

0 Yes

3 No

How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?..

Answer / gaurav jain

begin
select distinct * into #one from four where id in (select
id from four group by id
having count(*)>1)
delete from four where id in (select id from four group by
id having count(*)>1)
insert into four select * from #one
end

Is This Answer Correct ?

0 Yes

3 No

How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?..

Answer / anoop rajan

Tbale emp had some duplicate entries and i wanted to retain
the first of all duplicates, the others could be deleted as
follows . Please give your comments if this is the most
optimum way :

delete from emp where rowid in
(select rowid from emp o where rowid !=
(select min(rowid) from emp i where i.empno=o.empno));

Is This Answer Correct ?

0 Yes

3 No

How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?..

Answer / pawan k. dubey

delete from employee
where Emp_id not in (select min(Emp_id) from employee
group by Emp_Name)

Is This Answer Correct ?

2 Yes

12 No

Post New Answer