How can we update columns values on multiple rows with a single MySQL UPDATE statement?
Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.
Example
Suppose we have a table ‘tender’ as follows −
mysql> Select * from tender;
+-----------+---------+------+
| tender_id | company | rate |
+-----------+---------+------+
| 200 | ABC | 1000 |
| 300 | ABD | 5000 |
| 301 | ABE | 6000 |
| 302 | ABF | 2500 |
| 303 | ABG | 2600 |
+-----------+---------+------+
5 rows in set (0.00 sec)
Now if we want to update the ‘rate’ column where tender_id is greater than or equal to 300 then we can use the following query −
mysql> UPDATE tender SET rate = rate + 1000 WHERE tender_id >= 300;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4 Changed: 4 Warnings: 0
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)
We can observe from the above result set that the values in multiple rows, having tender_id >= 300, has been updated.
Published on 19-Feb-2018 16:25:29
