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
