Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting/Deleting rows with the same column information Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 2:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 35, Visits: 311
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!
Post #1447159
Posted Friday, April 26, 2013 2:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 20,458, Visits: 14,081
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1447163
Posted Friday, April 26, 2013 2:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 1,059, Visits: 5,749
Like this?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1447164
Posted Friday, April 26, 2013 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 20,458, Visits: 14,081
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1447180
Posted Saturday, April 27, 2013 11:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 08, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1447309
Posted Tuesday, April 30, 2013 3:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 35, Visits: 311
Thank you all for your help! Sorry for the data issue.
Post #1448264
Posted Tuesday, April 30, 2013 4:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 20,458, Visits: 14,081
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1448270
Posted Thursday, May 02, 2013 1:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 35, Visits: 311
Yes, thank you again!
Post #1448962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse