Tuesday, December 14, 2010

How can delete duplicate rows from table except one row?

This problem can be solved by many ways. But I have two simple method :-
1) using  simple sub query Method
2) using Rowcount

1) delete
   top (select count(*)-1 from TableName where Condition )
   from
 TableName where Condition .
Note :- Both condition should be same for apropriate result.

2) RowCount is very usefull keyword in sqlserver. By this way we can fix , how many row will be affected .
suppose in a table we have n number of rows , we want to display only 1 row , just write down
     set rowcount 1
after that any DML or DDL command effects only one row of  table.
and ,
    set rowcount 0 , for all rows from table
For the above problem , at first get number of similar rows from table using " select count(*) from TABLENAME  where Condtion " (suppose get n rows , where n is integer)
Then
set rowcount  n-1
delete  from  TABLENAME  where  Condition
set rowcount 0

1 comment:

  1. All duplicate row can be deleted with a single query as

    delete T from
    (
    select row_number()over(partition by column1 order by column2)a,* from table)T
    where a>1

    ReplyDelete