4

I have a table Emp which have records like this

Id     Name
1      A
2      B
3      C
1      A
1      A
2      B
3      C

Now I want to delete the duplicate rows from the table I am using this query to select or count number of duplicate records

SELECT NameCol, COUNT(*) as TotalCount FROM TestTable 
GROUP BY NameCol HAVING COUNT(*) > 1 
ORDER BY COUNT(*) DESC

and what query should i write to delete the duplicate rows from table.

if I write this query to delete the duplicate records then it is giving a (0) row Affected result.

`DELETE FROM TestTable 
    WHERE ID NOT IN ( SELECT MAX(ID) FROM 
                                     TestTable 
                                     GROUP BY NameCol
                    )`
Farax
  • 1,447
  • 3
  • 20
  • 37
Ankur Gupta
  • 933
  • 3
  • 15
  • 27
  • You want to just delete them or you want to use get the count for the distinct records? You can use the distinct keyword for the latter – Farax Sep 24 '13 at 09:31
  • 1
    possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – t-clausen.dk Sep 24 '13 at 09:40

2 Answers2

6

For sqlserver 2005+

Testdata:

declare @t table(Id int, Name char(1))
insert @t values
(1,'A'),(2,'B'),(3,'C'),(1,'A'),(1,'A'),(2,'B'),(3,'C')

Delete statement(replace @t with your Emp table)

;with a as
(
select row_number() over (partition by id, name order by id) rn
from @t
)
delete from a where rn > 1

select * from @t
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
-3
**Q How to Remove duplicate data with help of Rowid**   


create table abcd(id number(10),name varchar2(20))

insert into abcd values(1,'abc')

insert into abcd values(2,'pqr')


insert into abcd values(3,'xyz')

insert into abcd values(1,'abc')

insert into abcd values(2,'pqr')

insert into abcd values(3,'xyz')


select * from abcd

id  Name
1   abc
2   pqr
3   xyz
1   abc
2   pqr
3   xyz

Delete Duplicate record but keep Distinct Record in table 

DELETE 
FROM abcd a
WHERE ROWID > (SELECT MIN(ROWID) FROM abcd b
WHERE b.id=a.id
);

run the above query 3 rows delete 

select * from abcd

id  Name 
1   abc
2   pqr
3   xyz
Krunal Patel
  • 29
  • 2