SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding and Deleting Duplicate Data


Finding and Deleting Duplicate Data

Author
Message
Peter Meagher
Peter Meagher
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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?
Peter Meagher
Peter Meagher
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
vishnup
vishnup
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search