Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
1K+ Views
