Re: reset auto_increment
Mike, set insert_id=1; can the auto_increment value be reset back to '1' withou recreate the entire table again ?? The table would be emptied first, but we would like to reset the auto_increment value back to '1' without having to drop and recreate the table, if possible. Regards Trevor Rhodes === Powered by Linux- Mandrake 9.2 Registered Linux user # 290542 at http://counter.li.org Registered Machine #'s 186951 Mandrake Club Silver Member Source : my 100 % Microsoft-free personal computer. === 10:13:58 up 17:21, 1 user, load average: 1.42, 1.39, 1.22 -- Never mud wrestle with a pig.. you get dirty and the pig enjoys it! Never try to teach a pig to dance. You waste your time and annoy the pig. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto_increment
At 16:06 -0500 1/10/03, INVALID - TESTING ONLY. IF YOU GET EMAIL WITH THIS ACCOUNT P wrote: I need to reset the auto_increment in a table full of data. I had a problem recently where some script was putting InvoiceID numbers into an auto_increment CustID column...I since fixed the problem and corrected the data but I now have a huge gap in my number sequence and I cant get auto_increment reset to a more reasonable value. Now I have read the archives and the online manual with user comments and nothing has worked as of yet. What is your table type? ALTER TABLE tbl_name AUTO_INCREMENT = 1 Should work. this command gives me a successful response but when I do a SHOW TABLE STATUS the Auto_increment is still unchanged. What value does it show, and what is the maximum value still in your table? If you expect this to set the next value *lower than the current maximum value in the table*, it won't. That's not how it works. myisamchk -A=1 /path/to/db.MYI -A=1 is invalid syntax. Should be -A1 (no space) or --set-auto-increment=1 this command also gives me a successful message but never changes the auto_increment when displayed by SHOW TABLE STATUS in section 3.5.9 of the MySQL manual with user comments I noted the following comment on Oct 23 20002 by Ethan Pooley http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html He stated that the ALTER TABLE tbl_name AUTO_INCREMENT = 1 will only work when the table is empty. That's incorrect. So what I need is the ability to reset the auto_increment without having to empty the table first. Or I need someone to help me figure out why the above mentioned commands fail to do what everyone tells me they are suppose to do. -Jason - 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: Reset auto_increment
The following deletes all records and re-starts renumbering from 1; DELETE FROM mytable; The following deletes all records and continues counting from where the table last left off: DELETE FROM mytable WHERE 1; If you are trying to restart numbering while not deleting records, forget-about-it. -Original Message- From: Wei Gao [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:10 PM To: [EMAIL PROTECTED] Subject: Reset auto_increment Hi, As I am new to MySQL, perhaps this is a common question you have been asked. I have a field Id which using auto_increment. Now I want to reset the value of auto_increment, that is the Id starts form 1 and does not skip the number. I have spent a few hours to find a way, but all failed. Could you please tell me how to do it? Wei _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com - 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: Reset auto_increment
The following deletes all records and re-starts renumbering from 1; DELETE FROM mytable; The following deletes all records and continues counting from where the table last left off: DELETE FROM mytable WHERE 1; If you are trying to restart numbering while not deleting records, forget-about-it. -Original Message- From: Wei Gao [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:10 PM To: [EMAIL PROTECTED] Subject: Reset auto_increment Hi, As I am new to MySQL, perhaps this is a common question you have been asked. I have a field Id which using auto_increment. Now I want to reset the value of auto_increment, that is the Id starts form 1 and does not skip the number. I have spent a few hours to find a way, but all failed. Could you please tell me how to do it? Wei _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com - 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: Reset auto_increment
At 14:57 -0600 2/7/02, Rick Emery wrote: The following deletes all records and re-starts renumbering from 1; DELETE FROM mytable; The following deletes all records and continues counting from where the table last left off: DELETE FROM mytable WHERE 1; If you are trying to restart numbering while not deleting records, forget-about-it. ALTER TABLE tbl_name DROP id, ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, AUTO_INCREMENT = 1; But why do this? MySQL doesn't care if the sequence has holes in it or not. -Original Message- From: Wei Gao [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:10 PM To: [EMAIL PROTECTED] Subject: Reset auto_increment Hi, As I am new to MySQL, perhaps this is a common question you have been asked. I have a field Id which using auto_increment. Now I want to reset the value of auto_increment, that is the Id starts form 1 and does not skip the number. I have spent a few hours to find a way, but all failed. Could you please tell me how to do it? Wei - 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: reset auto_increment?
Can someone tell me how to reset a column's auto_increment starting point back to 0 in mysql ? Thanks Paul According to Paul DuBois on page 169 of MySQL by New Riders (which you should buy): ALTER TABLE t DROP i, ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY Jay - 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: reset auto_increment?
At 3:15 PM -0400 9/17/01, Jay Fesco wrote: Can someone tell me how to reset a column's auto_increment starting point back to 0 in mysql ? Thanks Paul According to Paul DuBois on page 169 of MySQL by New Riders (which you should buy): ALTER TABLE t DROP i, ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY Jay Actually, this will drop the column and renumber the rows sequentially. To just reset the sequence to 0 (well, to 1, since sequences start at 1), do this: ALTER TABLE t AUTO_INCREMENT = 1 -- Paul DuBois, [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
