How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
How to compare the top two records using sql?
Tell me what are the essential components of sql server service broker?
What is lock escalation and what is its purpose?
How do I find the sql server database version?
what is hash table
what is the difference in login security modes between v6.5 and 7.0? : Sql server database administration
What is a mutating table error and how can you get around it?
How to use copy and concatenate commands in SQL?
How to replace null values in expressions using isnull()?
Do you know what is fill factor and pad index?
Where is trigger in sql server?
Explain database normalization?