How can I update a field in a MySQL database table by adding a value in the second table with a value from the first table?

Let us first create a table −

mysql> create table DemoTable1
   (
   value int
   );
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(10);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

Output

+-------+
| value |
+-------+
| 10 |
+-------+
1 row in set (0.00 sec)

Following is the query to create second table −

mysql> create table DemoTable2
   (
   value1 int
   );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(50);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable2 values(100);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

Output

+--------+
| value1 |
+--------+
| 50     |
| 100    |
+--------+
2 rows in set (0.00 sec)

Now, update a field in a MySQL database table by adding a value from the first table to a single value in the second table −

mysql> set @myValue=(select value from DemoTable1);
Query OK, 0 rows affected (0.00 sec)
mysql> update DemoTable2 set value1=value1+@myValue;
Query OK, 2 rows affected (0.11 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable2;

Output

+--------+
| value1 |
+--------+
| 60     |
| 110    |
+--------+
2 rows in set (0.00 sec)

Arjun Thakur

Published on 14-Jun-2019 11:29:02