How to change primary key values

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 ecommerce website, and you want your order number to begin 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 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`

| table | CREATE TABLE `table` (
`id` int(3) unsigned NOT NULL AUTO_INCREMENT,

You may find other MySQL functions and operator useful. For example, if you wish to replace incorrectly added backslashes from a table column:

UPDATE table_name SET col = REPLACE(col, '\\', '')

MySQL functions and operators