SQLServerCentral / Discuss Content Posted by Chris Cubley / Article Discussions / Article Discussions by Author / Finding and Deleting Duplicate Data / Latest Posts

RE: Finding and Deleting Duplicate Data

vishnup — Fri, 13 Mar 2009 04:12:36 GMT

Hi,Great Post. This will definately help me. I tried manipulating your code to work for me and i get an "Msg 207, Level 16, State 1, Line 27 Invalid column name 'iID'." when i want o delete the duplicates and retail one copy of the duplicates. The error is with p2.iid. The column is there and i am not sure why i am getting this error.Can you please help?--deleteing duplicated rows with keeping 1 copy of the duplicated row.DELETE FROM p1FROMVehicleHistory p1INNER JOIN ( SELECT MAX(iID) AS ColumnID, ivehicleid, dtdatetime, iOdometer FROM VehicleHistory GROUP BY ivehicleid, dtdatetime, iOdometer HAVING COUNT(*) > 1) p2 ON (p1.ivehicleid = p2.ivehicleid AND p1.dtdatetime = p2.dtdatetime AND p1.iOdometer = p2.iOdometer and p1.iID <> p2.iID)

RE: Finding and Deleting Duplicate Data

Peter Meagher — Wed, 31 Oct 2007 15:59:32 GMT

To Calvin Lawson - I should hav added the modified code as follows:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE SP_TEST01 ASBEGIN SET NOCOUNT ON; if object_ID('dbo.TBLTEST01') is not null drop table TBLTEST01CREATE TABLE TBLTEST01( ID int identity Not NUll Primary Key, CustNum int NOT NULL, PostedDate datetime NOT NULL, PaymentAmt money NOT NULL, PaymentMemo varchar(200) NOT NULL, CCRefNum char(10) NOT NULL)Insert TBLTEST01 Values (1, '01/10/2004', 1, '', '')Insert TBLTEST01 Values (1, '01/11/2004', 1, 'Keep', '')Insert TBLTEST01 Values (2, '01/10/2004', 2, '', '')Insert TBLTEST01 Values (2, '01/11/2004', 2, '', '')Insert TBLTEST01 Values (2, '01/12/2004', 2, 'Keep', '')Insert TBLTEST01 Values (3, '01/10/2004', 3, '', '')Insert TBLTEST01 Values (3, '01/11/2004', 3, '', '')Insert TBLTEST01 Values (3, '01/12/2004', 3, '', '')Insert TBLTEST01 Values (3, '01/13/2004', 3, 'Keep', '')delete p1From TBLTEST01 p1 JOIN TBLTEST01 p2 on p1.CustNum = p2.CustNum and p1.PostedDate < p2.PostedDate and p1.PaymentAmt = p2.PaymentAmt where p1.ID < p2.IDENDThanks again

RE: Finding and Deleting Duplicate Data

Peter Meagher — Wed, 31 Oct 2007 15:56:04 GMT

Thank you to Calvin Lawsen, Posted 7/26/2004 6:55 PM. I tried and it worked first pass. However, I had to modify a bit to make the process delete the records with the earliest date and time stamp, leaving only the most recent record. However, upon closer scrutiny, I noticed that the second part of your code segment, i.e. beginning with "DELETE FROM p1......" didn't seem necessary?! The first code segment worked just fine.Am I missing something or was this an oversight?

RE: Finding and Deleting Duplicate Data

Calvin Lawson — Mon, 26 Jul 2004 11:55:00 GMT

Here's the full script:

if object_ID('tempdb..#Payment') is not null drop table #PaymentCREATE TABLE #Payment( PaymentID int identity Not nUll Primary Key, CustomerNumber int NOT NULL, PostedDatetime datetime NOT NULL, PaymentAmt money NOT NULL, PaymentMemo varchar(200) NOT NULL, CCRefNum char(10) NOT NULL)

Insert #Payment Values (1, '01/01/2004', 1, '', '')Insert #Payment Values (1, '01/01/2004', 1, '', '')Insert #Payment Values (2, '01/02/2004', 2, '', '')Insert #Payment Values (2, '01/02/2004', 2, '', '')Insert #Payment Values (2, '01/02/2004', 2, '', '')Insert #Payment Values (3, '01/03/2004', 3, '', '')Insert #Payment Values (3, '01/03/2004', 3, '', '')Insert #Payment Values (3, '01/03/2004', 3, '', '')Insert #Payment Values (3, '01/03/2004', 3, '', '')

delete p1From #Payment p1 JOIN #Payment p2 on p1.CustomerNumber = p2.CustomerNumber and p1.PostedDateTime = p2.PostedDateTime and p1.PaymentAmt = p2.PaymentAmt where p1.PaymentID < p2.PaymentID

DELETE FROM p1FROM #Payment p1INNER JOIN ( SELECT MAX(PaymentID) AS PaymentID, CustomerNumber, PostedDatetime, PaymentAmt FROM #Payment GROUP BY CustomerNumber, PostedDatetime, PaymentAmt HAVING COUNT(*) > 1 p2 ON( p1.CustomerNumber = p2.CustomerNumber AND p1.PostedDatetime = p2.PostedDatetime AND p1.PaymentAmt = p2.PaymentAmt AND p1.PaymentID <> p2.PaymentID

RE: Finding and Deleting Duplicate Data

Calvin Lawson — Mon, 26 Jul 2004 11:53:00 GMT

This is a pretty good technique, but there are problems with it. Using derived tables in a query can be very powerful, but they can also cause excessive use of tempdb (similar to using # tables). Often, there are other ways of doing the same thing for cheaper.

So, if you don't have to use them, don't. The query below is functionally equivalent to the delete statement in the article, but it costs almost half.

delete p1From Payment p1 JOIN Payment p2 on p1.CustomerNumber = p2.CustomerNumber and p1.PostedDateTime = p2.PostedDateTime and p1.PaymentAmt = p2.PaymentAmt where p1.PaymentID < p2.PaymentID

RE: Finding and Deleting Duplicate Data

cppwiz — Tue, 29 Jul 2003 13:18:00 GMT

Just to follow up, I followed all of the sample code and everything worked great the first time. The article was well written and the logic was clear in describing why the steps were necessary. Keep up the good work !!!

RE: Finding and Deleting Duplicate Data

icata — Tue, 29 Jul 2003 10:18:00 GMT

I think you can use this approach too--table structureCREATE TABLE [a4] ( [id] [int] IDENTITY (1, 1) NOT NULL , [title] [varchar] (50) , [name] [varchar] (50) NULL , CONSTRAINT [PK_a4] PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY]GO--to have distinct id for the doubles--the first id'sselect distinct a.id, a.title, a.name from a4 a, a4 b where a.title = b.title and a.name = b.name and a.id < b.id --the last id'sselect distinct a.id, a.title, a.name from a4 a, a4 b where a.title = b.title and a.name = b.name and a.id > b.id --to delete the first id's doubles and get unique rows in the original tabledelete from a4 where id in (select distinct a.id from a4 a, a4 b where a.title = b.title and a.name = b.name and a.id < b.id )--to delete the last id's doubles and get unique rows in the original tabledelete from a4 where id in (select distinct a.id from a4 a, a4 b where a.title = b.title and a.name = b.name and a.id > b.id )

RE: Finding and Deleting Duplicate Data

currym — Sat, 26 Jul 2003 17:53:00 GMT

Good Article! Thanks.

RE: Finding and Deleting Duplicate Data

cppwiz — Fri, 25 Jul 2003 07:49:00 GMT

Excellent article ! This is something I plan to use on one of my tables later today.

RE: Finding and Deleting Duplicate Data

whunt — Fri, 25 Jul 2003 05:49:00 GMT

This is not as complex as using temporary tables and scrubbing processes and then moving to live tables.That was my first atempt.

RE: Finding and Deleting Duplicate Data

shepton — Fri, 25 Jul 2003 03:45:00 GMT

Useful topic to cover but isn't the final solution over-complex?Couldn't you just use an inequality of PaymentID's on a self-join as follows..?DELETE PaymentWHERE PaymentID IN (SELECT P1.PaymentID FROM Payment P1 INNER JOIN Payment P2 ON P1.CustomerNumber = p2.CustomerNumber AND p1.PostedDatetime = p2.PostedDatetime AND p1.PaymentAmt = p2.PaymentAmt AND p1.PaymentID > p2.PaymentID)Mark

RE: Finding and Deleting Duplicate Data

Graham Farrow — Fri, 25 Jul 2003 02:11:00 GMT

Neatly encapsulates several techniques in one place. I will save it in my bits of useful code.

Finding and Deleting Duplicate Data

ccubley@queryplan.com — Sun, 13 Jul 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ccubley/findinganddeletingduplicatedata.asp