Click here to Skip to main content
15,881,967 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have tried a sql query to find the number of duplicate records in a table:
SQL
select count(*) from table_name a
where rowid >(select min(rowid)
                        from table_name b
                     where a.column1=b.column1
and a.Column2 = b.Column2)


This returned me a result of 1203 rows.
Now when i try to run the below query to delete these rows, the application hangs and stops responding.

SQL
delete from table_name a
where rowid >select min(rowid)
                        from table_name b
                     where a.column1=b.column1
and a.column2= b.column2)


Is there any other way to do it? Please help
Posted
Updated 23-May-11 5:42am
v2

 
Share this answer
 
Have a look at following article
http://support.microsoft.com/kb/139444[^]
 
Share this answer
 
1. Create another table temp or physical for temporary use
2. Insert distinct record in that table
3. Trunace the existing one which has duplicate rows
4. Insert rown back form temp table to original table
5. truncate / delete temp table

If data is large then use another physical table with same structure as of original....

Thanks
 
Share this answer
 
v2
Comments
Prathibha Sharat 23-May-11 9:45am    
Hi,
Thanks for the reply. Can you also help me with the commands i need to use as I am not very familiar with SQL.
 
Share this answer
 
Comments
Prathibha Sharat 24-May-11 9:35am    
The steps mentioned in the procedure asks me to add an identity column but I am not able to do so:-
alter table table_name
add column_name int not null Identity(1,1);
But this doesnt work. Any solution?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900