[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

how to delete duplicate rows in table sql server 2005

I have 6 identical rows of 4 columns table in sql server 2005
I like to have a script to delete the duplicate rows
Is there a way to list rowid ( oracle concept) along with the columns to delete one of the duplicate rows

1 Solution
See template code below using a temporary table for testing, you should be able to adapt the delete statement below to suit your needs -
create table #tmp (a int, b int,c int, d int)
insert #tmp values (0,0,0,0)
insert #tmp values (1,1,1,1)
insert #tmp values (2,2,2,2)
insert #tmp values (3,3,3,3)
insert #tmp values (4,4,4,4)
insert #tmp values (5,5,5,5)
go 2
select * from #tmp
delete tmp 
from	(select *, row_number() over (partition by a,b,c,d order by a,b,c,d) as rn
	from #tmp) tmp
where rn > 1
drop table #tmp

Open in new window

You can use a Row Number or a RANK function:

if you RANK them just in the "partition by" field list ALL the fields then delete where Rank is <> '1'

Similar for Row
Delete  from MyTable where ID Not IN
     select  max(ID) from MyTable Group By Duplicate Colum1, Duplicate Column2,Duplicate Column 3
Table must have identity column which would identify the duplicate records. Here in MyTable ID as identity column  and duplicate columns are duplicate columns 1,2 & 3 (very obvious to know...!!!)
Also refer :   http://support.microsoft.com/kb/139444

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now