Delete Duplicate Records

[hisham123]

hi,

i had more duplicate records in my table.
i want to keep one rest i want to delete. can you give me sql server 2000 query

[DonlonP]

hi,

i had more duplicate records in my table.
i want to keep one rest i want to delete. can you give me sql server 2000 query

You could create a temporary table and insert all distinct records in there. Then truncate your original and repopulate with the contents of your temporary table.

[ck9663]

You could create a temporary table and insert all distinct records in there. Then truncate your original and repopulate with the contents of your temporary table.

if it's a full duplicate record, meaning all columns of two or more rows are the same, try:

select * distinct into TempTable from YourTable

drop YourTable

select * intoYourTable from TempTable

you have to consider constraints, indices, triggers, etc

[DonlonP]

if it's a full duplicate record, meaning all columns of two or more rows are the same, try:

select * distinct into TempTable from YourTable

drop YourTable

select * intoYourTable from TempTable

you have to consider constraints, indices, triggers, etc

I'm guessing you mean TRUNCATE YourTable

[hisham123]

hi suppose like following records

I had the table with 5 columns and many rows.
Now i want to delete the records which meets column1,column2,column3 are same
and keep one record in it.

Create Table T1 (Number int,name1 varchar(25), name2 varchar(25), name3 varchar(25), name4 varchar(25) )

Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa1','bbb','qqq','ttt')
Insert Into T1 values(2,'www','xxx','yyy','zzz')
Insert Into T1 values(2,'www','xxx','nnn','mmm')
Insert Into T1 values(2,'www2','xxx','nnn','mmm')
Insert Into T1 values(3,'fff','ggg','hhh','iii')
Insert Into T1 values(3,'fff','ggg','rrr','lll')

Result shall be

1-aaa-bbb-ccc-dd1
1-aaa1-bbb-qqq-ttt
2-www-xxx-yyy-zzz
2-www2-xxx-nnn-mmm
3-fff-ggg-hhh-iii

[baju123]

hi suppose like following records

I had the table with 5 columns and many rows.
Now i want to delete the records which meets column1,column2,column3 are same
and keep one record in it.

Create Table T1 (Number int,name1 varchar(25), name2 varchar(25), name3 varchar(25), name4 varchar(25) )

Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa1','bbb','qqq','ttt')
Insert Into T1 values(2,'www','xxx','yyy','zzz')
Insert Into T1 values(2,'www','xxx','nnn','mmm')
Insert Into T1 values(2,'www2','xxx','nnn','mmm')
Insert Into T1 values(3,'fff','ggg','hhh','iii')
Insert Into T1 values(3,'fff','ggg','rrr','lll')

Result shall be

1-aaa-bbb-ccc-dd1
1-aaa1-bbb-qqq-ttt
2-www-xxx-yyy-zzz
2-www2-xxx-nnn-mmm
3-fff-ggg-hhh-iii

You can implement this Using ROWCOUNT in Sql Server

[baju123]

You can implement this Using ROWCOUNT in Sql Server

http://www.sql-server-performance.com/dv_delete_duplicates.asp

use ROWCOUNT carefully.
specify 'SET NOCOUNT ON' to reset the count set by rowcount or else it may affect the normal operation