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.

Jai Janardhan

Published on 19-Feb-2018 11:55:29

• Related Questions & Answers
• Update multiple columns of a single row MySQL?
• Update multiple values in a table with MySQL IF Statement
• Update multiple rows in a single column in MySQL?
• Update two columns with a single MySQL query
• Update multiple rows in a single MongoDB query?
• How can we update values in a MySQL table?
• How to update multiple rows using single WHERE clause in MySQL?
• How to update multiple rows and left pad values in MySQL?
• How can we update any value in MySQL view as we can update the values in MySQL table?
• MySQL update multiple records in a single query?
• Update with multiple values in MySQL WHERE clause
• Can we update MySQL with if condition?
• How can we change MySQL user password by using UPDATE statement?
• Can we update a row with the highest ID in a single MySQL query?
• Concatenate multiple rows and columns in a single row with MySQL