Selecting/Deleting rows with the same column information

Question

Selecting/Deleting rows with the same column information

Viewing 7 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Here is a quick article on how to dedupe data

http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

Like this?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

ChrisM@home (4/26/2013)
Like this?

A few of them today eh?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Calibear Ten Centuries Points: 1377 More actions · Answer 4

Hogpen,

Next time it would be helpful to present the data like this, then we could easily duplicate the data and write the queries:

CREATE TABLE Accounting

(ProdDate DateTime,

AcctDate DateTime,

MtrNo int,

MtrSfx char(1),

TrnxID int,

RecCode char (2)

)

insert into Accounting

select '2/1/2011','8/1/2011','365','A','8301','RR' union all

select '2/1/2011','2/1/2013','365','A','8301','RR' union all

select '2/1/2011','2/1/2011','365','A','8301','OR' union all

select '3/1/2011','9/1/2011','365','A','8302','RR' union all

select '3/1/2011','3/1/2013','365','A','8302','RR' union all

select '3/1/2011','3/1/2011','365','A','8302','OR'

Here is the delete statement that I've come up with:

DELETE Accounting

FROM Accounting a,

(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate

FROM Accounting

WHERE RecCode = 'RR'

GROUP BY MtrNo, MtrSfx, TrnxID, RecCode

HAVING COUNT(TrnxID) > 1) b

where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx

and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode

and a.AcctDate = b.AcctDate

You need to group the data by all the primary key fields and then join that data on the original table and select. I find it is always helpful to write my code as a delete and just before I delete it, run it as a select to make sure I have the right code:

select a.*

FROM Accounting a,

(SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate) AcctDate

FROM Accounting

WHERE RecCode = 'RR'

GROUP BY MtrNo, MtrSfx, TrnxID, RecCode

HAVING COUNT(TrnxID) > 1) b

where a.MtrNo = b.MtrNo and a.MtrSfx = b.MtrSfx

and a.TrnxID = b.TrnxID and a.RecCode = b.RecCode

and a.AcctDate = b.AcctDate

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

hogpen SSC Eights! Points: 931 More actions · Answer 5

Thank you all for your help! Sorry for the data issue.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

hogpen (4/30/2013)
Thank you all for your help! Sorry for the data issue.

Were you able to get it resolved?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

hogpen SSC Eights! Points: 931 More actions · Answer 7

hogpen

SSC Eights!

Points: 931

May 2, 2013 at 1:30 pm

#1612011

Yes, thank you again!