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 <[email protected]> > > 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:231198 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
