How to perform Increment in MySQL Update?

To update values incrementally in MySQL, you need to create a variable with the help of SET command. The syntax to create a variable is as follows −

set @anyVariableName := 0;

To update value, you need to use UPDATE command. Let us begin with creating a table. The query to create a table −

mysql> create table UpdateValueIncrementally
   −> (
   −> ProductId int
   −> );
Query OK, 0 rows affected (0.90 sec)

Insert records in the table with the help of select statement. The query is as follows −

mysql> insert into UpdateValueIncrementally values(10);
Query OK, 1 row affected (0.15 sec)

mysql> insert into UpdateValueIncrementally values(100);
Query OK, 1 row affected (0.16 sec)

mysql> insert into UpdateValueIncrementally values(1000);
Query OK, 1 row affected (0.09 sec)

mysql> insert into UpdateValueIncrementally values(110);
Query OK, 1 row affected (0.17 sec)

mysql> insert into UpdateValueIncrementally values(102);
Query OK, 1 row affected (0.11 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from UpdateValueIncrementally;

The following is the output −

+-----------+
| ProductId |
+-----------+
|        10 |
|       100 |
|      1000 |
|       110 |
|       102 |
+-----------+
5 rows in set (0.00 sec)

The following is the query to update values incrementally −

mysql> set @incrementValue := 33333;
Query OK, 0 rows affected (0.00 sec)

A variable is created above and the value is initialized to 33333. The following is the query to update the values and incrementing −

mysql> update UpdateValueIncrementally set ProductId = (select @incrementValue := @incrementValue + @incrementValue);
Query OK, 5 rows affected (0.21 sec)
Rows matched: 5 Changed: 5 Warnings: 0

In the above query, I have incremented the value with the current value of @incrementValue. Now you can check whether the value is updated or not −

mysql> select *from UpdateValueIncrementally ;

The following is the output −

+-----------+
| ProductId |
+-----------+
|     66666 |
|    133332 |
|    266664 |
|    533328 |
|   1066656 |
+-----------+
5 rows in set (0.00 sec)