delete duplicate records from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

delete duplicate records from table

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-07-25 : 20:10:55

Hi,
My table has a field called as RateID. I want to keep only unique RateIDs. Can anyone tell me how to delete duplicate RateIDs?

Thanks in anticipation,
Mavericky

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-25 : 20:15:48

Here you go: http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-25 : 20:19:17

If you are on SQL 2005 or later, you can use row_number function to do this. Run the code below to see what will be deleted. If you are satisfied, comment out the last line and uncomment the line before that and run it to delete.

with cte as
( 
   select *,ROW_NUMBER() over (PARTITION by RateId order by (select null)) as RN
   from YourTable 
)
-- delete from cte where RN > 1;
select * from cte where RN > 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-25 : 20:20:16

Darn!!

rameshduraikannu
Starting Member

10 Posts

Posted - 2011-07-26 : 06:36:51

create table #temp(RateID int)

insert into #temp(RateID)values(100)
insert into #temp(RateID)values(100)

set rowcount 1

delete from #temp where rateid=100

Set rowcount 0

Select *from #temp

drop table #temp

note :set rowcount 'please mention the repeated row count no '

rameshduraikannu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 07:22:03

quote:
Originally posted by rameshduraikannu

create table #temp(RateID int)

insert into #temp(RateID)values(100)
insert into #temp(RateID)values(100)

set rowcount 1

delete from #temp where rateid=100

Set rowcount 0

Select *from #temp

drop table #temp

note :set rowcount 'please mention the repeated row count no '

rameshduraikannu

Important

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

rameshduraikannu
Starting Member

10 Posts

Posted - 2011-07-26 : 07:32:40

Hi webfred

Thanks for your update.

rameshduraikannu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources