reset auto increment to a lesser value
Hello friends, I need to reset auto increment to a lesser value, is there a metod to do so in any version of mysql. Pl. help me. Thanks abhi
Re: reset auto increment to a lesser value
On Sat, Aug 2, 2008 at 10:49 PM, Nacho Garcia [EMAIL PROTECTED] wrote: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html mysql *ALTER TABLE tbl AUTO_INCREMENT = 100;* On Sat, Aug 2, 2008 at 5:28 PM, abhishek jain [EMAIL PROTECTED] wrote: Hello friends, I need to reset auto increment to a lesser value, is there a metod to do so in any version of mysql. Pl. help me. Thanks abhi Hi Nacho, Thanks for the reply, But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start from those row-ids. Pl. reply, Thanks, Abhi
Re: reset auto increment to a lesser value
abhishek jain wrote: On Sat, Aug 2, 2008 at 10:49 But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start from those row-ids. That can't be done. An auto-increment value must be higher than the highest existing value. Otherwise, it would not be an auto-increment value at all - it would be an auto-interpolate. On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
Chris W wrote: Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Yes, but the relevant factor here is that in the table where the auto-increment value is generated it has no meaning other than as a unique id. In the other tables that use it as a reference, then it has meaning there and needs to be inserted as a known value. An auto-increment field can only be used where that value never needs to be set by reference to an external value. It can be a value that other external values are set to (such as in a one-to-many relationship), but in the other tables that use it as a reference then it isn't inserted as an auto-increment. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Truncate do not reset auto increment counter
Kim G. Pedersen [EMAIL PROTECTED] wrote: Kim G. Pedersen [EMAIL PROTECTED] wrote: I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. What table type do you use? TRUNCATE TABLE reset auto_increment value for MyISAM table, but not for InnoDB. thanks Egor I use InnoDB , so that explains it :-) what Are the most easy way to reset the value in innodb 4.014 If you want to reset auto_increment value, you should recreate table. If you want to start sequence from particu lar value insert a dummy row with value=wanted_value-1 and delete this row. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Truncate do not reset auto increment counter
Kim G. Pedersen [EMAIL PROTECTED] wrote: I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. What table type do you use? TRUNCATE TABLE reset auto_increment value for MyISAM table, but not for InnoDB. thanks Egor I use InnoDB , so that explains it :-) what Are the most easy way to reset the value in innodb 4.014 regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Truncate do not reset auto increment counter
hello I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. Mysql Linux 4.014 regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Truncate do not reset auto increment counter
Kim G. Pedersen [EMAIL PROTECTED] wrote: I once red that using truncate table xx instead of delete from xx should reset autoincrement , but it seem not to happend. but when I try to insert data with insert or load data in it remember the old autoincrement value. from manual : Truncate operations drop and re-create the table, which is much faster than deleting rows one by one. seems not , when I drop table and the create table again it works perfect. What table type do you use? TRUNCATE TABLE reset auto_increment value for MyISAM table, but not for InnoDB. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reset auto-increment value
I have a number of databases installed under mysql 3.23 and 4.0, and will need to occasionally re-initialize certain tables. Can I re-set auto-increment fields within those tables? Thanks Carlin Anderson - 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 value
At 21:00 -0500 12/29/02, Carlin Anderson wrote: I have a number of databases installed under mysql 3.23 and 4.0, and will need to occasionally re-initialize certain tables. Can I re-set auto-increment fields within those tables? You can drop and re-create the tables. This will work for any table type. For ISAM tables, if you delete all the records, that will automatically reset the counter. For MyISAM tables, you can delete all the records, then use: ALTER TABLE tbl_name AUTO_INCREMENT = 1 which will reset the counter. If what you mean is that you want to leave the existing records intact, but resequence the AUTO_INCREMENT column so that the sequence doesn't have holes in it, then I'd ask: why bother? Thanks Carlin Anderson - 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
I just saw this in the manual and think it can do what you need: It's an option of the myisamchk program --set-auto-increment[=value] Force auto_increment to start at this or higher value. If no value is given, then sets the next auto_increment value to the highest used value for the auto key + 1. Hope it helps John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 26 September 2001 00:03 To: [EMAIL PROTECTED] Subject: Reset Auto Increment Hi All, I seen a post a few days ago on how to set the auto increment value back to one after deleting all the data from a table. I suppose I could just re-create the table, but thought it be simpler to just alter the table to reset the auto increment value back to 1. what exactly is the alter syntax to reset it?? thx's mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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 6:03 PM -0500 9/25/01, [EMAIL PROTECTED] wrote: Hi All, I seen a post a few days ago on how to set the auto increment value back to one after deleting all the data from a table. I suppose I could just re-create the table, but thought it be simpler to just alter the table to reset the auto increment value back to 1. what exactly is the alter syntax to reset it?? If you saw a post, then the link at the bottom of each message indicates how to get to the list archive. You'll find the answer there. Often a useful strategy. In any case, the answer is: ALTER TABLE tbl_name AUTO_INCREMENT = 1 thx's mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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 -- 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
Reset Auto Increment
Hi All, I seen a post a few days ago on how to set the auto increment value back to one after deleting all the data from a table. I suppose I could just re-create the table, but thought it be simpler to just alter the table to reset the auto increment value back to 1. what exactly is the alter syntax to reset it?? thx's mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
trying to reset auto-increment
I manually entered into my database several rows with high values in the auto-increment column. I subsequently deleted the high value rows. I now am trying to reset the auto-increment. How do I reset it to the Max of the currently existing values? -matt [EMAIL PROTECTED] Ecosandals.com Making sandals, enriching lives Kutengeneza akala, kuinua maisha - 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: trying to reset auto-increment
At 11:05 PM -0400 9/16/01, [EMAIL PROTECTED] wrote: I manually entered into my database several rows with high values in the auto-increment column. I subsequently deleted the high value rows. I now am trying to reset the auto-increment. How do I reset it to the Max of the currently existing values? ALTER TABLE tbl_name AUTO_INCREMENT = 1 -matt [EMAIL PROTECTED] Ecosandals.com Making sandals, enriching lives Kutengeneza akala, kuinua maisha -- 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
