Delete duplicate records from the table?(Table must have
unique id)

Answers were Sorted based on User's Feedback



Delete duplicate records from the table?(Table must have unique id)..

Answer / dinesh kumar

delete from emp where id Not in(select max(id) id from emp
group by name having count(id)>1)

Is This Answer Correct ?    22 Yes 9 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / lince

DELETE
FROM MyTable
WHERE ID IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1 HAVING COUNT(ID)>1
)

Is This Answer Correct ?    4 Yes 2 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / manoj

Check following blog post on how to first identify & then delete duplicate records: http://sqlwithmanoj.wordpress.com/2011/10/14/identify-delete-duplicate-records-from-a-table/


This lists multiple ways to delete duplicates.

Is This Answer Correct ?    2 Yes 0 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / kk

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Is This Answer Correct ?    7 Yes 7 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / naren

delete id from table where id in(select id from table having count(id)>1)

Is This Answer Correct ?    1 Yes 2 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / krishna

select *
from mytable b
join
(
select Duplicatecolumn,MAX(id) id
from mytable
group by Duplicatecolumn
) b1 on b.Duplicatecolumn = b1.Duplicatecolumn
where b.id <> b1.id

Is This Answer Correct ?    0 Yes 1 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / brijesh darmwal, sandhya

DELETE FROM [MyDb].[dbo].[sandhya]
WHERE id
IN
(SELECT id
FROM
(SELECT MAX(id) as id,name,addr
FROM [MyDb].[dbo].[sandhya] GROUP BY name,addr having
count(id)>=2)
Tmp)

Is This Answer Correct ?    7 Yes 16 No

Post New Answer

More SQL Server Interview Questions

What should be the fill factor for indexes created on tables? : sql server database administration

0 Answers  


What is the optimization being performed in oracle and SQL Server?

0 Answers   Cap Gemini,


How many types of the database links?

0 Answers   MCN Solutions,


What is SQL Server?

0 Answers   Atos Origin,


write an SQL query to list the employees who joined in the month of January?

0 Answers   Agilent, Amdocs,






What is the use of partition by in sql server?

0 Answers  


Can a rule be bound to any column of any data type?

0 Answers  


Define left outer join?

0 Answers  


What are the ways available in sql server to execute sql statements?

0 Answers  


When you first load SQL SERVER you will startup with what all databases?

3 Answers   CompuSol,


What is a document index?

0 Answers  


What is difference between after and before?

0 Answers  


Categories