This will give you the emails in question, but it doesn't give you the ID's that you need in order to remove them safely. Ben's idea of joining the table to itself (earlier post):
"select id from emails as a, emails as b where a.id > b.id and a.email = b.email" is darn near brilliant in my view, as I've always struggled to wrap my hands around this problem. You could then wrap a delete statement around that above select query to knock them out in one punch. Thanks Ben! Mike -----Original Message----- From: Russ [mailto:[EMAIL PROTECTED] Sent: Thursday, February 02, 2006 3:01 PM To: CF-Talk Subject: RE: db eamil cleaner Select emailAddress from emails group by emailAddress having count(emailAddress) >1 And then do whatever you need to do with these ids... > -----Original Message----- > From: Ben Doom [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 02, 2006 3:52 PM > To: CF-Talk > Subject: Re: db eamil cleaner > > select id from emails as a, emails as b where a.id > b.id and a.email = > b.email > > This should give you your list of dups. > > --Ben > > dave wrote: > > The problem with that is when you use distinct and then add the ID field > it no longer grabs only one email because the id field is a distinct > number as well so you get all the records back not just one of each. > > > > ~Dave the disruptor~ > > google will pay you money to getting rid of ie :) > > http://explorerdestroyer.com/ > > http://www.killbillsbrowser.com/ > > > > ---------------------------------------- > > From: "Burns, John D" <[EMAIL PROTECTED]> > > Sent: Thursday, February 02, 2006 3:25 PM > > To: CF-Talk <cf-talk@houseoffusion.com> > > Subject: RE: db eamil cleaner > > > > I don't have a script but the basic idea would look like this: > > > > Select distinct email addresses and the id with it. Then delete all > > records whose ID is not in that list. Then, I'd use a regex to loop over > > the emails, compare them to the regex and if it's not valid, delete the > > record. I hope that helps. > > > > John Burns > > Certified Advanced ColdFusion MX Developer > > Wyle Laboratories, Inc. | Web Developer > > > > -----Original Message----- > > From: dave [mailto:[EMAIL PROTECTED] > > Sent: Thursday, February 02, 2006 3:18 PM > > To: CF-Talk > > Subject: Re: db eamil cleaner > > > > really only 2 fields: id (primary) and tbl_email, pretty simple > > > > ~Dave the disruptor~ > > google will pay you money to getting rid of ie :) > > http://explorerdestroyer.com/ http://www.killbillsbrowser.com/ > > > > ---------------------------------------- > > From: Rick Root > > Sent: Thursday, February 02, 2006 3:15 PM > > To: CF-Talk > > Subject: Re: db eamil cleaner > > > > dave wrote: > >> anyone have a good script to run through a mysql db and remove > > duplicate(but leave 1) and improper emails? > > > > That would totally depend on your database design =) > > > > Rick > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231209 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
