Remove duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove duplicate records

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-05 : 22:59:46

Hi Forumer's

Upn checking from my table I just encounter about 362 records with duplicate entries. I just want retain 1 records of the duplicate records. kindly please help me to modify the scripts below the suit to my requirements.


--DATA
declare @tmp table
(esn nvarchar(35), model_code nvarchar(35), supplier int,  shipdate datetime)
insert into @tmp values('270113179916697013','P333-4410-EVO',40,'2011-09-28 00:00:00.000')
insert into @tmp values('270113179916697013','P333-4410-EVO',40,'2011-09-28 00:00:00.000')
insert into @tmp values('268435456203849506','P300-1710-DROID',45,'2011-08-23 00:00:00.000')
insert into @tmp values('268435456203849506','P300-1710-DROID',45,'2011-08-23 00:00:00.000')
insert into @tmp values('268435456203849508','P300-1710-DROID2',42,'2011-09-20 00:00:00.000')


--SCRIPT
select 
      ESN,
      shipdate,
      COUNT(ESN) as total
from @tmp
group by 
ESN,
shipdate
having COUNT(ESN) > 1
Order by ESN

Or

Declare @esn nvarchar(35), shipdate datetime
Set @esn='268435456203849506'
set @shipdate ='2011-08-23 00:00:00.000'

DELETE FROM @tmp
WHERE ESN=@esn AND shipdate =@shipdate