Execute operations (plus, minus, multiply, divide) while updating a MySQL table?
Following is the syntax executing the plus (+) operator −
update yourTableName set yourColumnName3=(yourColumnName1+yourColumnName2)
The above syntax is only for plus operator. You need to change symbol like -,*,/ for other operations. Let us first create a table −
mysql> create table DemoTable
-> (
-> Number1 int,
-> Number2 int,
-> AddResult int,
-> MinusResult int,
-> MultiplyResult int,
-> DivideResult int
-> );
Query OK, 0 rows affected (0.89 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable(Number1,Number2) values(40,20);
Query OK, 1 row affected (0.16 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+---------+---------+-----------+-------------+----------------+--------------+
| Number1 | Number2 | AddResult | MinusResult | MultiplyResult | DivideResult |
+---------+---------+-----------+-------------+----------------+--------------+
| 40 | 20 | NULL | NULL | NULL | NULL |
+---------+---------+-----------+-------------+----------------+--------------+
1 row in set (0.00 sec)
Following is the query to execute operations like plus, minus, multiply and divide while using UPDATE in MySQL −
mysql> update DemoTable set AddResult=(Number1+Number2);
Query OK, 1 row affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update DemoTable set MinusResult=(Number1-Number2);
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update DemoTable set MultiplyResult=(Number1*Number2);
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update DemoTable set DivideResult=(Number1/Number2);
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Let us check the table records once again −
mysql> select *from DemoTable;
This will produce the following output −
+---------+---------+-----------+-------------+----------------+--------------+
| Number1 | Number2 | AddResult | MinusResult | MultiplyResult | DivideResult |
+---------+---------+-----------+-------------+----------------+--------------+
| 40 | 20 | 60 | 20 | 800 | 2 |
+---------+---------+-----------+-------------+----------------+--------------+
1 row in set (0.00 sec)
Published on 09-Jul-2019 18:07:13
