Delete Duplicate Data - 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 Duplicate Data

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-18 : 09:59:13

I accidently added duplicate data into a table. All of the data has a unique primarycode that is Rateid. I am trying to select the duplicates first and then delete them out. Below is an image of the table and then the code to follow.

I believe you need to assign the primary key rateid to somthing but I am not sure how to make this work. Anyone who can help it would be much appriciated.

Thanks,
Graham

Columns in Table Table1
RateID (PK)
ApplicationID (FK)
ProgramID (FK)
CoverageID (FK)

Attemted query.......did not work. But I am trying to isolate the unique PK that share the same data for all other colums.

SELECT Rateid1.RateID, Rateid2.RateID, ApplicationID, ProgramID, CoverageID, LimitCoverageID, LimitMin, LimitMax, RateCalcCoverageID, Rate, CalcType, RateType, ExcessCoverageID,
ExcessLimit, MaxofExcessLimit, EffectiveDate, ExpirationDate, PCMin, PCMax, Territory
FROM Table1
Where (Rateid1.Rateid > rateid2.rateid) and Programid between 1895 and 1912 and ApplicationID = ApplicationID and ProgramID = ProgramID and CoverageID = CoverageID and LimitCoverageID = LimitCoverageID and LimitMin = LimitMin and LimitMax = LimitMax and RateCalcCoverageID = RateCalcCoverageID and Rate = Rate and CalcType = CalcType and RateType = RateType and ExcessCoverageID = ExcessCoverageID and ExcessLimit = ExcessLimit and MaxofExcessLimit = MaxofExcessLimit and EffectiveDate = EffectiveDate and ExpirationDate = ExpirationDate and PCMin = PCMin and PCMax = PCMax and Territory = Territory

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-18 : 11:50:06

To have a look:
select * from
(
row_number() over(
partition by
ApplicationID, ProgramID, CoverageID, LimitCoverageID, LimitMin, LimitMax, RateCalcCoverageID, Rate, CalcType, RateType, ExcessCoverageID, ExcessLimit, MaxofExcessLimit, EffectiveDate, ExpirationDate, PCMin, PCMax, Territory
order by RateID
) as rownum,
*
from YourTable
)dt

To delete the duplicates and hold the row with the min(RateID):
delete from
(
row_number() over(
partition by
ApplicationID, ProgramID, CoverageID, LimitCoverageID, LimitMin, LimitMax, RateCalcCoverageID, Rate, CalcType, RateType, ExcessCoverageID, ExcessLimit, MaxofExcessLimit, EffectiveDate, ExpirationDate, PCMin, PCMax, Territory
order by RateID
) as rownum,
*
from YourTable
)dt
where rownum > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-18 : 12:02:07

THANK you SO MUCH! I have a new post coming soon that I think you will fin interesting. Thank you for your time.

Best,
GG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-18 : 12:09:28

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-18 : 13:16:57

I was hoping that you could insert into your query these variables......
Database= Rating_2
table = Ratedata

I do not follow row_number and rownum. In the table RateID is the first row if that helps.

Thanks again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-18 : 13:57:21

quote:
Originally posted by webfred

To have a look:
select * from
(
row_number() over(
partition by
ApplicationID, ProgramID, CoverageID, LimitCoverageID, LimitMin, LimitMax, RateCalcCoverageID, Rate, CalcType, RateType, ExcessCoverageID, ExcessLimit, MaxofExcessLimit, EffectiveDate, ExpirationDate, PCMin, PCMax, Territory
order by RateID
) as rownum,
*
from Rating_2..Ratedata
)dt

To delete the duplicates and hold the row with the min(RateID):
delete from
(
row_number() over(
partition by
ApplicationID, ProgramID, CoverageID, LimitCoverageID, LimitMin, LimitMax, RateCalcCoverageID, Rate, CalcType, RateType, ExcessCoverageID, ExcessLimit, MaxofExcessLimit, EffectiveDate, ExpirationDate, PCMin, PCMax, Territory
order by RateID
) as rownum,
*
from Rating_2..Ratedata
)dt
where rownum > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-18 : 14:10:19

I got this error again

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources