Updating auto_increment ID Column/Selecting Duplicate Rows
hi, i have 2 questions that i badly need answered. i use phpmyadmin, but any answers containing SQL syntax should work in this app... 1) this should be simple but i don't know it. i use the following mysql table field as the Key for my tables: id int(10) unsigned NOT NULL auto_increment, now, when i have to clean up items in my tables, like when users have sent off a .html form multiple times and i want to delete duplicate submissions, how can i quickly easily update all of the 'id' fields in the entire table, to reflect the numeration change? now, using phpmyadmin, if i go in and delete a few entries, the id's have gaps in them. 2) in a table i have a column called User. some people have entered things into this table multiple times. how do i create a SELECT statement to select only those rows that have multiple Users? i need something like this (but this is wrong, of course): SELECT * FROM mail_form2 WHERE 'count(User) 1' many thanks from a newbie, tom - 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: Updating auto_increment ID Column/Selecting Duplicate Rows
Tom Churm wrote: hi, i have 2 questions that i badly need answered. i use phpmyadmin, but any answers containing SQL syntax should work in this app... 1) this should be simple but i don't know it. i use the following mysql table field as the Key for my tables: id int(10) unsigned NOT NULL auto_increment, now, when i have to clean up items in my tables, like when users have sent off a .html form multiple times and i want to delete duplicate submissions, how can i quickly easily update all of the 'id' fields in the entire table, to reflect the numeration change? now, using phpmyadmin, if i go in and delete a few entries, the id's have gaps in them. You don't reorder them. The gaps remain. Besides, if the id is used in any other table, changing a value would be a serious data integrity issue. 2) in a table i have a column called User. some people have entered things into this table multiple times. how do i create a SELECT statement to select only those rows that have multiple Users? i need something like this (but this is wrong, of course): SELECT * FROM mail_form2 WHERE 'count(User) 1' many thanks from a newbie, tom What things, besides user names, would people be entering here? If you are concerned about duplicate user names, add a unique index on this column, you can use 'ALTER IGNORE mail_form2 add unique (User)', but I'm not sure this is what you meant. - 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 -- Gerald L. Clark [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
RE: Updating auto_increment ID Column/Selecting Duplicate Rows
This would give you a list of all users that have entered things more than once; However, it would not give you all the rows that are duplicated. SELECT Count(User) from mail_form2 GROUP BY User HAVING Count(User) 1 Hope this helps! Daniel Von Fange -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 2) in a table i have a column called User. some people have entered things into this table multiple times. how do i create a SELECT statement to select only those rows that have multiple Users? i need something like this (but this is wrong, of course): SELECT * FROM mail_form2 WHERE 'count(User) 1' SELECT * from mail_form2 GROUP BY User HAVING Count(User) 1 - 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