any reason not to select distinct into a tmp table? On Sun, 10 Feb 2002, DL Neil wrote:
> 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 > --------------------------------------------------------------------- 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
