How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?

MySQLMySQLi Database

Existing values of the row can be used to provide new values in the SET clause if that row matches the WHERE clause in an UPDATE statement. Following is the example to demonstrate it.

Example

Suppose we have a table named ‘tender’ as follows −

mysql> Select * from tender;
+-----------+---------+------+
| tender_id | company | rate |
+-----------+---------+------+
| 200       | ABC     | 1000 |
| 300       | ABD     | 6000 |
| 301       | ABE     | 7000 |
| 302       | ABF     | 3500 |
| 303       | ABG     | 3600 |
+-----------+---------+------+
5 rows in set (0.00 sec)

The query below will increase values in column tender_id by 100 and also update the ‘rate’ column in accordance with new ‘tender_id’.

mysql> UPDATE tender SET tender_id = tender_id + 100, rate = tender_id + 500 Where tender_id > 300;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> Select * from tender;
+-----------+----------+------+
| tender_id | company  | rate |
+-----------+----------+------+
| 200       | ABC      | 1000 |
| 300       | ABD      | 5000 |
| 401       | ABE      | 901  |
| 402       | ABF      | 902  |
| 403       | ABG      | 903  |
+-----------+----------+------+
5 rows in set (0.00 sec)
raja
Published on 19-Feb-2018 12:03:45
Advertisements