To change values of existing integer field (`table` is the table name, and "id" is the column name), you may use the following update SQL command:
update `table` set id=id+10000;
Let's say that you have an e-commerce website, and you want your order number to begin at 10001. But, you already have a handful of orders already so you'll just update the existing values.
If you're updating your primary key values, MySQL will automatically adjust the auto_increment value to begin after the largest number stored in the database.
To reset your auto-increment value in MySQL database:
alter table `table` auto_increment = 1;
Notice, we are setting the auto_increment value to 1. By assigning 1, MySQL will automatically find the largest number and increment by 1. You may verify this by "SHOW CREATE TABLE" MySQL command.
% mysql mysql> show create table `table` CREATE TABLE `table` ( `id` int(3) unsigned NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`id`), ) ENGINE=MyISAM AUTO_INCREMENT=6132 DEFAULT CHARSET=latin1
You may find other MySQL functions and operators useful. For example, if you wish to replace incorrectly added backslashes from a table column:
UPDATE table_name SET col = REPLACE(col, '\\', '')
References
Share this post
Leave a comment
All comments are moderated. Spammy and bot submitted comments are deleted. Please submit the comments that are helpful to others, and we'll approve your comments. A comment that includes outbound link will only be approved if the content is relevant to the topic, and has some value to our readers.
Comments (0)
No comment