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:231195 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
