List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:August 28 2001 1:46pm
Subject:Re: Updating auto_increment ID Column/Selecting Duplicate Rows
View as plain text  

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 <mysql-thread83771@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
Updating auto_increment ID Column/Selecting Duplicate RowsTom Churm28 Aug
  • Re: Updating auto_increment ID Column/Selecting Duplicate RowsGerald Clark28 Aug
  • RE: Updating auto_increment ID Column/Selecting Duplicate RowsDaniel Von Fange28 Aug