Selecting/Deleting rows with the same column information

Question

Post reply

Selecting/Deleting rows with the same column information

hogpen

SSC Eights!

Points: 931
More actions
April 26, 2013 at 2:38 pm

#292755

I need to delete some rows from our system that have the same transaction id based on an accounting date. Our primary key for the table is MtrNo, MtrSfx, TrnxID, RecCode.
Here's some example data:
ProdDate | AcctDate | MtrNo |MtrSfx | TrnxID | RecCode
------------------------------------------------------
2/1/2011 | 8/1/2011 | 365 |A | 8301 | RR
2/1/2011 | 2/1/2013 | 365 |A | 8301 | RR
2/1/2011 | 2/1/2011 | 365 |A | 8301 | OR
3/1/2011 | 9/1/2011 | 365 |A | 8302 | RR
3/1/2011 | 3/1/2013 | 365 |A | 8302 | RR
3/1/2011 | 3/1/2011 | 365 |A | 8302 | OR
I want to be able to delete all the most recent rows based on AcctDate that have an RR RecCode. So in the above table, I'd want to delete:
2/1/2011 | 2/1/2013 | 365 |A | 8301 | RR
3/1/2011 | 3/1/2013 | 365 |A | 8302 | RR
It seems to be simple but I'm not very good with T-SQL. I would assume I need a sub-query that uses max, group by, and count.
Would something like the query below work?
DELETE FROM Tbl
WHERE (SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate)
FROM Tbl
WHERE RecCode = 'RR'
GROUP BY MtrNo, MtrSfx, TrnxID, RecCode
HAVING COUNT(TrnxID) > 1)
The select seems to give me the right results but I don't know how the delete statement works and if it would just delete the columns I bring back.
Any help would be greatly appreciated!

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

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

SQLRNNR SSC Guru Points: 281334 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: 281334 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: 281334 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!