Re: Updating auto_increment ID Column/Selecting Duplicate Rows

2001-08-28 Thread Gerald Clark 



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

2001-08-28 Thread Daniel Von Fange 

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