Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

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

Comments

Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...