RE: duplicate records check
Title: RE: duplicate records check Hi Neil, try something like this SELECT your_field FROM your_table GROUP BY your_field HAVING COUNT(*) 1 Mit freundlichen Grssen Frank Kalis Asset Management ProACTIV___ CiV Versicherungen * PB Versicherungen * PB Pensionsfonds AG Neustrae 62, 40721 Hilden tel +49 (0) 21 03-34 - 7282 fax +49 (0) 21 03-34 - 7098 mailto:[EMAIL PROTECTED] internet: www.proactiv.de -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: duplicate records check Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicate records check
Hi Neil, 1) You could ask for the count of each listed item in a column. something like: SELECT item, count(item) from parts GROUP BY item; 2) you could then use SELECT id,item from parts WHERE item = 'xxx'; to review the double entries. This is just a general sample, perhaps if you give more details than we can be more specific. ;-) Best regards Nils Valentin Tokyo/Japan 2003 7 9 15:42Neil Tompkins : Could any one advise what SQL statement I would need to use, to check a table for any duplicate records e.g that contain the same data within a field. Note that I haven't got the field as a unqiue field. Thanks Neil _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate records
Hello Steve, Do a select on the record before you insert it. If the record does not exist in the database (i.e. mysql_num_rows () == 0) than it is safe to do the insert. John -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 3:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate records
You can always catch the Duplicate Key error and respond accordingly in your PHP script. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 1:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate records
It is almost safe, but not entirely, since another user could slip in a row with your number in the split second between your Select ... and your Insert. That is the case for blocking the Select ... Insert sequence as one logical unit of work so the target table is locked from Select through Insert. PB - - Original Message - From: John Griffin To: Steve Marquez ; MySQL List Cc: PHP eMail List Sent: Friday, June 06, 2003 2:51 PM Subject: RE: Duplicate records Hello Steve, Do a select on the record before you insert it. If the record does not exist in the database (i.e. mysql_num_rows () == 0) than it is safe to do the insert. John -Original Message- From: Steve Marquez [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 3:41 PM To: MySQL List Cc: PHP eMail List Subject: Duplicate records Hello. I am adding a record to a MySQL Database using PHP. I have the records listed by the field: $id_num. I want the DB to be updated only with new id numbers, and return an error if there is a duplicate number already in the DB. Here is the code I am using: ?php $insert_data = INSERT into articles Values ( 'a href=\upload_form.php?id_num=$id_num\edit/a', 'a href=\delete.php?id_num=$id_num\delete/a', '$id_num', '$title', '$author', '$article_contents', '$start_date', '' );; $response = mysql_query( $insert_data, $dbh ); $get_table_data = SELECT * FROM ccfs; $response = mysql_query( $get_table_data, $dbh ); ? Can anyone help me? Hope this makes sense. Thanks! Steve Marquez Marquez Design [EMAIL PROTECTED] www.marquez-design.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate Records
A quick method is to create a new table: CREATE TABLE newtable SELECT DISTINCT * FROM oldtable; note; manually verify newtable has your data. Then proceed: DROP TABLE oldtable; CREATE TABLE oldtable SELECT * FROM newtable; DROP TABLE newtable; -Original Message- From: Rich [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 10, 2002 10:31 AM To: MySql Subject: Duplicate Records 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. 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? TIA Rich -- [EMAIL PROTECTED] - 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
Re: Duplicate Records
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
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
Re: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: Rich [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records 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. 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? TIA Rich -- [EMAIL PROTECTED] - 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
Re: Duplicate Records
Dan, [I've cc-ed this to the list and to Rich - who asked the original question] 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. This is one of a couple of rather neat SQL methods for dealing with duplicates - which strictly-speaking answers Rich's question. What is its disadvantage? The problem with a 'delete' technique is that no real record is kept of the action - which in a commercial situation makes auditors and other 'suits' unhappy. Even in a personal situation it can make life difficult because you don't know what you've got till it's gone! Consequently when it comes to 'deletions', us worry-worts tend to take the longer-winded/more boring approach, eg doing a SELECT before a DELETE, because then one is sure of two things: 1 the actual data being DELETEd (and an audit trail for those that regard such things as 'light reading'); and 2 that there is no 'slight error' in the deletion criteria (or assumptions about the data) which leads to a major 'oops'. A visual inspection row by row (as I recommended) on a table of millions of records would be totally impractical - automated methods would be the only way to go! Regards, =dn 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
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
Re: Duplicate Records
Hi, You can try to use ALTER IGNORE TABLE syntax : ALTER IGNORE TABLE your_table ADD UNIQUE(Id); Regards, Jocelyn Fournier - Original Message - From: Rich [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Sunday, February 10, 2002 5:31 PM Subject: Duplicate Records 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. 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? TIA Rich -- [EMAIL PROTECTED] - 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
RE: Duplicate Records
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
