delete duplcate date 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

SQL Server 2008 Forums

Transact-SQL (2008)

delete duplcate date records

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-08-08 : 11:16:36

hi
want to delete records from a table that have the sames dates only want one date in there for each record
some else wrote this code but its not right.

SET ROWCOUNT 1

DELETE FROM dbo.TempSyntheticNAV WHERE ( SELECT COUNT(*) FROM dbo.TempSyntheticNAV ee WHERE dbo.TempSyntheticNAV.Date = ee.Date) > 1

WHILE (@@rowcount > 0)
BEGIN
    DELETE FROM dbo.TempSyntheticNAV  WHERE ( SELECT COUNT(*) FROM dbo.TempSyntheticNAV ee WHERE dbo.TempSyntheticNAV.Date = ee.Date) > 1
END

SET ROWCOUNT 0

what changes will make it do what i want

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 12:08:55

Something like this:
[CODE]

DECLARE @TABLE1 TABLE(ID INT, PET VARCHAR(10), Name VARCHAR(10));

INSERT INTO @TABLE1 VALUES
(1, 'Cat', 'Newton'),
(2, 'Cat', 'Pumpkin'),
(3, 'Cat', 'Newton'),
(4, 'Dog', 'Roth'),
(5, 'Dog', 'Spark'),
(6, 'Dog', 'Spark'),
(7, 'Dog', 'Buster'),
(8, 'Dog', 'Arrow');

DELETE D FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Pet, Name ORDER BY ID) RN, Pet, Name from @Table1) D
WHERE D.RN > 1;

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources