Delete Duplicates in XREF Table

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 2005 Forums

Transact-SQL (2005)

Delete Duplicates in XREF Table

Author

Topic

HGClubTec
Starting Member

12 Posts

Posted - 2010-09-17 : 11:09:24

I have a table that sporatically gets duplicates and causes problems. My goal is complete a delete query that I can run that will delete the newest of the entries.

So here is my current query...

select
ID,
RevenueCodeID,
TaxID

from
ctcreceivables.dbo.revenuecodetaxxref R

Where R.RevenueCodeID IN
(Select
RevenueCodeID

from
ctcreceivables.dbo.RevenueCodeTaxXREF X

Group By X.RevenueCodeID, X.TaxID

HAVING
Count(RevenueCodeID) > 1 AND Count(TaxID) > 1)

Order By RevenueCodeID,ID

And my results are:

ID RevenueCodeID TaxID
55 351 6
56 351 6
57 351 6
58 351 6
84 359 6
114 359 6

I'd like to delete ID's 56, 57, 58 & 114.

I feel like I'm close to at least extracting the data I'd like to delete, but I cant't figure out the last part.

TIA for any help I get!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-17 : 11:24:32

[code]-- Sample Data
DECLARE @T TABLE (ID INT, RevenueCodeID INT, TaxID INT)

INSERT @T
VALUES
(55, 351, 6),
(56, 351, 6),
(57, 351, 6),
(58, 351, 6),
(84, 359, 6),
(114, 359, 6)

-- AS Derived Table
DELETE
T
FROM
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY RevenueCodeID, TaxID ORDER BY ID) AS RowNum
FROM @T
) AS T
WHERE
RowNum > 1

-- Or using a CTE
;WITH cteRev
AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY RevenueCodeID, TaxID ORDER BY ID) AS RowNum
FROM @T
)
DELETE cteRev
WHERE RowNum > 1[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-17 : 11:30:36

Or for fun as a join:

DELETE
	T
FROM
	@T AS T
INNER JOIN
	(
		Select
			MIN(ID) AS ID,
			RevenueCodeID,
			TaxID
		from
			@T AS X
		Group By 
			X.RevenueCodeID, X.TaxID
		HAVING
			COUNT(*) > 1
	) AS D
	ON T.RevenueCodeID = D.RevenueCodeID
	AND T.TaxID = D.TaxID
WHERE
	T.ID <> D.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 11:58:34

quote:

Originally posted by Lamprey

-- Sample Data
DECLARE @T TABLE (ID INT, RevenueCodeID INT, TaxID INT)

INSERT @T
VALUES
(55, 351, 6),
(56, 351, 6),
(57, 351, 6),
(58, 351, 6),
(84, 359, 6),
(114, 359, 6)

-- AS Derived Table
DELETE
	T
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY RevenueCodeID, TaxID ORDER BY ID) AS RowNum
		FROM @T
	) AS T
WHERE
	RowNum > 1
	


-- Or using a CTE
;WITH cteRev
AS
(
	SELECT
		*
		,ROW_NUMBER() OVER (PARTITION BY RevenueCodeID, TaxID ORDER BY ID) AS RowNum
	FROM @T	
)
DELETE cteRev
WHERE RowNum > 1

actually you dont need that * in inner query. you just need to return only rownum from inner query for deletion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-17 : 12:18:11

That is true. I tend to leave it in there for the forums to make it easier for people to run just the select and see what data it reutns. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources