If i run your statement i get a a return value of 17,000 records If I run a DISTINCT statement I get 26,000 records
I think at this point the best thing to do will be to make another table and roll a distinct query and loop through and add the records. This is the result of their old site which didnt check for dup entries and the new one does, so shouldn't be 2 bad. ~Dave the disruptor~ google will pay you money to getting rid of ie :) http://explorerdestroyer.com/ http://www.killbillsbrowser.com/ ---------------------------------------- From: "Russ" <[EMAIL PROTECTED]> Sent: Thursday, February 02, 2006 4:04 PM To: CF-Talk <[email protected]> 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" > > Sent: Thursday, February 02, 2006 3:25 PM > > To: CF-Talk > > 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:231210 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
