Blog Post

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: (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

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

insert into temp(cust_id)

values

('oms1'),

('oms2'),

('oms3')

Method 2: (Using Top clause)

--Remove 1 by 1

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

select *from temp

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

                 

Method 3: (If your data is small then you can use this

way.Here I craeted a table with distict 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

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 4: [Using ROW_NUMBER() and CTE.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

                  --cust_id

                  --oms2

                  --oms1

                  --oms3

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating