MySQL increment a database field by 1?

MySQLMySQLi Database

You can increment a database using update command. The syntax is as follows −

UPDATE yourTableName
set yourColumnName=yourColumnName+1
where condition;

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table IncrementBy1
   -> (
   -> Id int,
   -> Name varchar(100),
   -> CounterLogin int
   -> );
Query OK, 0 rows affected (0.63 sec)

Insert some records using insert command. The query to insert records in the table is as follows −

mysql> insert into IncrementBy1 values(100,'John',30);
Query OK, 1 row affected (0.17 sec)

mysql> insert into IncrementBy1 values(101,'Carol',50);
Query OK, 1 row affected (0.15 sec)

mysql> insert into IncrementBy1 values(102,'Bob',89);
Query OK, 1 row affected (0.25 sec)

mysql> insert into IncrementBy1 values(103,'Mike',99);
Query OK, 1 row affected (0.18 sec)

mysql> insert into IncrementBy1 values(104,'Sam',199);
Query OK, 1 row affected (0.36 sec)

mysql> insert into IncrementBy1 values(105,'Tom',999);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from IncrementBy1;

Output

+------+-------+--------------+
| Id   | Name  | CounterLogin |
+------+-------+--------------+
|  100 | John  |           30 |
|  101 | Carol |           50 |
|  102 | Bob   |           89 |
|  103 | Mike  |           99 |
|  104 | Sam   |          199 |
|  105 | Tom   |          999 |
+------+-------+--------------+
6 rows in set (0.00 sec)

Here is the query that increments the database field by 1 −

mysql> update IncrementBy1
   -> set CounterLogin=CounterLogin+1
   -> where Id=105;
Query OK, 1 row affected (0.45 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now you can check the particular record is incremented or not. The value 999 has been incremented with 1 since we are incrementing the value where Id=105 as shown above.

The following is the query to check the record −

mysql> select *from IncrementBy1 where Id=105;

Output

+------+------+--------------+
| Id   | Name | CounterLogin |
+------+------+--------------+
|  105 | Tom  | 1        000 |
+------+------+--------------+
1 row in set (0.00 sec)
raja
Published on 10-Jan-2019 11:24:46
Advertisements