Complicated Delete - 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

Complicated Delete

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-01-31 : 09:16:58

I want to delete any duplicate rows. the only field that should be different in these two tables is RATEID. I want to delete a row of data if entire row of data matches another row of data, with the only exeption/ is the primary idenditfy Rateid which will be unique.

Below is an example innner join I use to find one long row of data. if you could help me convert this into a delete described above that would be a huge help!

Best
GG

SELECT RateData.*, Programs.*
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where programs.state = 'DE'and ratedata.limitmax = 100000 and ratedata.coverageid = 3 and ratedata.PCmax = 6 and programs.occupancytypeid = 2 and programs.DCmin = 1 and ratedata.territory = 'B' and programs.constructiontype = 1 and programs.policytype = 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 09:52:35

Please give table structure, example data and wanted result.

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

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-01-31 : 11:06:51

table 1----> Programs

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-01-31 : 11:27:43

Table 1---Programs----
Programid (PK)
ApplicationID (FK)
companyID
State
Description
OccupancyTypeID (FK)
PolicyType (FK)
Policytype (FK)
PolicyTerm (FK)
DCmin
DCmax
MinLosses
MinValue
MaxValue
MaxCredits
MaxPDCredit
MaxSchedule
ConstructionType (FK)
ClassCode
TIVLimit

Table2---Ratedata---

RateID (PK)
ApplicationID (FK)
ProgramID (FK)
CoverageID (FK)
LimitCoverageID
LimitMin
LimitMax
RateCalcCoverageID
Rate
Calctype
RateType
ExcessCoverageID
ExcessLimit
MaxofExcessLimit
EffectiveDate
ExpirationDate
PCmin
PCmax
Territory
TimeStamp

Here is the table structure. Now I want to delete any duplicates. But RateID will be unique. So if all more then one row has the same values for all consecutive collums and the only thing that seperates the row is RateID......I want to delete the dubplicate.

let me know if there is anything else you need to help. thank you very much for your time.

Best,
G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 11:36:11

Are there duplicate rows in both tables or only in Ratedata?
I'm asking that because there is no column named RateID in the Programs table.

And if there are duplicates in Ratedata then which row should stay?

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

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-01-31 : 11:51:35

I would have to say just in Ratedata. So I just want to Delete the duplicate row in ratedata.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 11:59:24

What datatype is the column TimeStamp?

Are you using SQL Server 2005 or higher?

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

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-01-31 : 13:22:37

I am using 2005, and timestamp is (timestamp,not null)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 16:41:14

So RateId isn't the only column with different values in duplicate rows!
A timestamp column is also always a different value.
Is there something more we should know?

You are very reserved with giving helpful informations...

But I will try to build a statement that can delete your duplicates...

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 16:47:45

[code]delete dt
from
(
select
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 DESC) as rownum,
*
from RateData
)dt
where rownum > 1
[/code]

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

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-02-01 : 08:19:27

Thanks for the help, I think that might work.

Best,
G

Jim Beam
Posting Yak Master

137 Posts

Posted - 2011-02-01 : 08:30:51

Would this lock the entire table for the duration of the statement? If so, would it be better to use a variable?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-01 : 08:41:52

I don't know what you mean ie. how you would like to use a variable.

I think it depends on how many rows are in that table and also I believe it is a statement that has to be executed only once, so the OP can do it in a not-so-busy time...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources