Jeff,
I am doing the same thing here. Very simple and to the point. You
probably won't need the 'company', but here is my exact code. Works fast
and like a charm!
----------------------------------
CREATE PROCEDURE [DeDupEmailAdd]
@Company varchar(2)
AS
Declare @TempEmail varchar(50)
Select @TempEmail = ''
Declare DeDup Cursor for
Select email
>From customers (index=IX_EMAIL)
Where email in (SELECT email
FROM customers
Where company = @Company
GROUP BY email
HAVING (COUNT(email) > 1))
Order by Email
for update
open DeDup
Set nocount on
declare @email varchar(50)
fetch next from DeDup into @Email
While @@fetch_Status = 0 Begin
if @Email = @tempEmail begin
Delete
From Customers
Where current of DeDup
end
select @TempEmail = @Email
fetch next from DeDup into @Email
end
close DeDup
Deallocate DeDup
GO
---------------------------------------------
Thank You,
Christian Watt
Webmaster
SkillPath Seminars
[EMAIL PROTECTED]
-----Original Message-----
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 25, 2003 5:08 PM
To: SQL
Subject: RE: This should be so easy, but I'm getting stumped...(duplicate
entries)
I was going to try writing a query using a cursor and see if that would work
to find and delete the dups, but when looking for good ref. I found
http://www.sql-server-performance.com/rd_delete_duplicates.asp
I think that is a very good example to do exactly what you are looking for.
(I think)
Mark W. Breneman
-Cold Fusion Developer
-Network Administrator
Vivid Media
[EMAIL PROTECTED]
www.vividmedia.com
608.270.9770
-----Original Message-----
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 24, 2003 1:11 PM
To: SQL
Subject: This should be so easy, but I'm getting stumped...(duplicate
entries)
Pseudocode:
"delete every entry in MyTable where MyTable.Email is duplicated"
Trying to take a list with possible duplicate entries and remove all the
duplicates except one so that all are left is a single instance of an email
address, regardless of capitalization
Table: EmailAddresses
Column: Email
That's all. It sounds deceptively easy, doesn't it? Lol...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX 6.1, now 2.5 times faster.
http://www.macromedia.com/software/coldfusion/productinfo/upgrade/jump/introducing.html?trackingid=ColdFusion_468x60g_HouseofFusion_carat_082803
Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1674
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
