You can do the following to remove duplicates: - create table foo as select distinct cols from table_name_containing_duplicates; - drop table_name_containing_duplicates; - alter table foo rename to table_name_containing_duplicates;
Done. Now, make a primay key for the new table!! -- this will avoid duplicates. Regards, Dan > -----Original Message----- > From: DL Neil [mailto:[EMAIL PROTECTED]] > Sent: Monday, 11 February 2002 8:30 a.m. > To: Rich; MySql > Subject: Re: Duplicate Records > > > Rich, > > > How does one go about removing one of two identical records in a MySQL > > database? My mistake in an earlier database was not applying a unique > > number to each record (1, 2, 3, ---). I know it's possible to use the > > DISTINCT operator to show only one of the identical records in a result, > > but that does not remove one of them from the database. > > =Ouch! > > =Do you have a particular criteria to delete one or other of any > duplicate records, or are they absolutely > identical (and therefore it doesn't matter which stays/goes)? > > =You cannot really risk automated deletion in either case! > - if the records are slightly different, criteria must be > established to determine which is right/should be > kept; > - if they are absolutely identical, how will you identify in a > WHERE clause that only one record of that > criteria is to be removed of two identical rows? > > =To identify the duplicate rows try:- > > SELECT column(s), COUNT(*) as duplicates > FROM tbl > GROUP BY key > HAVING duplicates > 1 > > =Depending upon the number of rows returned, you could then feed > those keys into a series of DELETE ... LIMIT 1 > commands - assuming duplicates are strictly 'two of'; otherwise > it might be easier to take the listing and do it > by hand (using a mgmt package). > > > One thought that I had would be to add a unique number to each record, > > and that could probably be done manually, one record at a time, but is > > there a way to automate the process so that unique numbers could be > > assigned with one command? > > =ALTER TABLE allows the addition of a new column. The only > question is whether to do it before (if it might help > the editing job) or after, weeding out the duplicates (to get a > more continuous AUTO_INCREMENT sequence, if it's > at all of interest). > > =Regards, > =dn > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
