How to update field to add value to existing value in MySQL?

MySQLMySQLi Database

You can update field to add value to an existing value with the help of UPDATE and SET command. The syntax is as follows −

UPDATE yourTableName SET yourColumnName = yourColumnName+integerValueToAdd WHERE yourCondition;

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

mysql> create table addingValueToExisting
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(30),
   -> GameScore int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.58 sec)

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

mysql> insert into addingValueToExisting(Name,GameScore) values('John',89);
Query OK, 1 row affected (0.11 sec)

mysql> insert into addingValueToExisting(Name,GameScore) values('Mike',56);
Query OK, 1 row affected (0.28 sec)

mysql> insert into addingValueToExisting(Name,GameScore) values('Sam',99);
Query OK, 1 row affected (0.18 sec)

mysql> insert into addingValueToExisting(Name,GameScore) values('Carol',100);
Query OK, 1 row affected (0.17 sec)

mysql> insert into addingValueToExisting(Name,GameScore) values('David',67);
Query OK, 1 row affected (0.25 sec)

mysql> insert into addingValueToExisting(Name,GameScore) values('Bob',78);
Query OK, 1 row affected (0.14 sec)

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

mysql> select *from addingValueToExisting;

The following is the output −

+----+-------+-----------+
| Id | Name  | GameScore |
+----+-------+-----------+
| 1  | John  |        89 |
| 2  | Mike  |        56 |
| 3  | Sam   |        99 |
| 4  | Carol |       100 |
| 5  | David |        67 |
| 6  | Bob   |        78 |
+----+-------+-----------+
6 rows in set (0.00 sec)

Update field to add a value to existing value. For our example, let us update 100 with the value 110 by adding 10. The query is as follows −

mysql> update addingValueToExisting set GameScore = GameScore+10 where Id = 4;
Query OK, 1 row affected (0.23 sec)
Rows matched − 1 Changed − 1 Warnings − 0

Check the specific record has been updated or not. The query is as follows −

mysql> select *from addingValueToExisting where Id = 4;

The following is the output −

+----+-------+-----------+
| Id | Name  | GameScore |
+----+-------+-----------+
| 4  | Carol | 110       |
+----+-------+-----------+
1 row in set (0.00 sec)

Look at the above output, the value 100 is incremented by 10, which is now 110.

raja
Published on 01-Feb-2019 10:04:24
Advertisements