August 4, 2008 at 7:47 am
How to delete duplicate rows in a table?
I had no Primary Key and Identity Column ON TABLE
empid ename
101 A
102 A
103 A
104 B
104 B
August 4, 2008 at 8:13 am
satish.thota2002 (8/4/2008)
How to delete duplicate rows in a table?I had no Primary Key and Identity Column ON TABLE
empid ename
101 A
102 A
103 A
104 B
104 B
One solution is to select DISTINCT * from this table into another table, then move the data back/rename the new table while getting rid of the original.
Another solution on SQL Server 2005 is to use a delete statement with row_number()
DELETE a
FROM ( SELECT *
, row_number() OVER ( PARTITION BY empid, ename ORDER BY empid ) AS rowNr
FROM myTable) a
WHERE rowNr > 1
Regards,
Andras
August 4, 2008 at 8:28 am
I think there are thousands of replies to a question like this in the forums.
The point is: why no primary key? Is there any good reason not to add a primary key to a table? I think there isn't.
Anyway, use a temp table to get rid of duplicates:
select *, cnt = row_number() over (partition by candidateKey, order by candidateKey)
into #tempTable
from sourceTable as a
where candidateKey in (
select candidateKey
from sourceTable
group by candidateKey
having count(*) > 1
)
--delete duplicates
delete from sourceTable
where candidateKey in (
select candidateKey
from #tempTable
)
insert into sourceTable
select * -- (I mean: all fields except the one you computed with row_count)
from #tempTable
where cnt = 1
This should work or at least give you some piece of advice, but please, use primary keys!;)
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply