Tuesday 19 March 2013

Remove duplicate records from a table in SQL server


Here I create a table and inserted some duplicate records for testing purpose.

create table temp(cust_id varchar(50))
insert into temp(cust_id)
values
('oms1'),
('oms2'),
('oms1'),
('oms2'),
('oms3')
select *from temp

Method 1[Using ROWCOUNT]: 
You can simply use ROWCOUNT to delete duplicate record.

      --deleting 1 by 1

set ROWCOUNT 1
DELETE from temp where cust_id='oms1'
SET ROWCOUNT 0

select *from temp
                  --cust_id
                  --oms2
                  --oms1
                  --oms2
                  --oms3
          
insert into temp (cust_id) values('oms1')

Deleting all duplicate rows 

SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
   BEGIN
        DELETE   FROM temp WHERE cust_id IN (SELECT  cust_id FROM temp GROUP BY          cust_id HAVING  COUNT(*) > 1)
   IF @@Rowcount = 0
         BREAK ;
   END
SET ROWCOUNT 0   


select *from temp

Output:
                  --cust_id
                  --oms1
                  --oms2
                  --oms3




Method 2[Using TOP Clause]: 

--Remove 1 by 1
delete top(1) from temp where cust_id='oms1'   

select *from temp

Output:
                  --cust_id
                  --oms2
                  --oms1
                  --oms2
                  --oms3
                 


Method 3[With the help of Another Table]: 
If your data is small then you can use this way.Here I created a table with distinct records.Drop first one and rename second one.

SELECT DISTINCT cust_id
INTO    temp1
FROM    temp
GO
DROP TABLE temp
exec sp_rename 'temp1', 'temp'

select *from temp

Output:
                  --cust_id
                  --oms1
                  --oms2
                  --oms3



Method 4[Using ROW_NUMBER]:
It is very efficient way to achieve the same.]  

WITH  TEST
          AS (
             SELECT   cust_id
             , row_number() OVER ( PARTITION BY cust_id ORDER BY cust_id )                     AS rowcnt
             FROM  temp
             )
          DELETE  FROM TEST
          WHERE  rowcnt  > 1
   

select *from temp

Output:
                  --cust_id
                  --oms2
                  --oms1
                  --oms3

No comments:

Post a Comment

Please leave a comment for this post