How can I delete duplicate records

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

SQL Server 2005 Forums

Transact-SQL (2005)

How can I delete duplicate records

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-06-05 : 08:16:33

Hi Guys,

I have the following table
customerid customername
------------------------
1 AAA
1 AAA
2 BBB
2 BBB
2 BBB
3 CCC
3 CCC

Here, I need to delete duplicate records from the above table.
After deleting the duplicate records the table should be
like this:
customerid customername
------------------------
1 AAA
2 BBB
3 CCC

Can any one help me!!!!!!

Regards
js.reddy

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-05 : 08:44:38

check out this topic and associated article:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

And in the future inprove your data integrity and performance by using Primary Keys (and Unique contraints when you have a surrogate key)

Be One with the Optimizer
TG

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-05 : 09:06:32

Try this
declare @tbl table(customerid int,customername varchar(50))

insert into @tbl
select 1, 'AAA' union all
select 1 ,'AAA' union all
select 2 ,'BBB' union all
select 2 ,'BBB' union all
select 2 ,'BBB' union all
select 3 ,'CCC' union all
select 3 ,'CCC'

delete t from
(select customerid,customername,row_number()over(partition by customerid,customername order by customerid,customername)as rowid
from @tbl)t where rowid<>1

select * from @tbl

And dont forget what TG said.

Vivek.Roberts
Starting Member

1 Post

Posted - 2008-06-05 : 09:09:03

select distinct column_name into temp_table from duplicate_table
drop table duplicate_table
select distinct column_name into duplicate_table from temp_table
drop table temp_table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources