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
