How to add +1 to existing MySQL values?

Let us see an example and create a table first.

mysql> create table Add1ToExistingValue
   -> (
   -> Value int
   -> );
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">10</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.12</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">13</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.15</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">15</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">16</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">20</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.16</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">40</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.15</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">50</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.11</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">55</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.17</span><span class="pln"> sec</span><span class="pun">)</span>
<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">56</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.17</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statement.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pun">;</span>

The following is the output

+-------+
| Value |
+-------+
| 10    |
| 13    |
| 15    |
| 16    |
| 20    |
| 40    |
| 50    |
| 55    |
| 56    |
+-------+
9 rows in set (0.00 sec)

Here is the query to add +1 to existing values

mysql> update Add1ToExistingValue set Value=Value+1 where Value >=20;
Query OK, 5 rows affected (0.08 sec)
Rows matched: 5 Changed: 5 Warnings: 0

Let us check the table records from the table using select statement.

The query is as follows

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Add1ToExistingValue</span><span class="pun">;</span>

The following is the output

+-------+
| Value |
+-------+
| 10    |
| 13    |
| 15    |
| 16    |
| 21    |
| 41    |
| 51    |
| 56    |
| 57    |
+-------+
9 rows in set (0.00 sec)