Finding and Deleting Duplicate Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Chris Cubley » Finding and Deleting Duplicate Data

13 posts, Page 2 of 2

««1 2

Finding and Deleting Duplicate Data

Rate Topic

Display Mode

Topic Options

Author

Message

Peter Meagher

Posted Wednesday, October 31, 2007 3:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2007 3:50 PM
Points: 2, Visits: 3

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?

Post #417340

Peter Meagher

Posted Wednesday, October 31, 2007 3:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2007 3:50 PM
Points: 2, Visits: 3

To Calvin Lawson - I should hav added the modified code as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SP_TEST01
AS
BEGIN
SET NOCOUNT ON;
if object_ID('dbo.TBLTEST01') is not null drop table TBLTEST01
CREATE 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 p1
From TBLTEST01 p1
JOIN TBLTEST01 p2
on p1.CustNum = p2.CustNum and
p1.PostedDate < p2.PostedDate and
p1.PaymentAmt = p2.PaymentAmt
where p1.ID < p2.ID
END

Thanks again

Post #417341

vishnup

Posted Friday, March 13, 2009 4:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 03, 2010 1:44 AM
Points: 5, Visits: 19

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
p1
FROM
VehicleHistory p1
INNER 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)

Post #675012

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions