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 update field to add value to existing value in MySQL?
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.
Updated on: 30-Jul-2019
7K+ Views
Advertisements