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

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

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

Question Posted / brijesh darmwal, sandhya

7 Answers
12089 Views
TCS, Thomson Reuters, I also Faced
E-Mail Answers

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