
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- Update multiple columns of a single row MySQL?
- Update multiple rows in a single column in MySQL?
- Update multiple values in a table with MySQL IF Statement
- Update two columns with a single MySQL query
- How to update multiple rows using single WHERE clause in MySQL?
- Update multiple rows in a single MongoDB query?
- How can we update values in a MySQL table?
- 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?
- Update with multiple values in MySQL WHERE clause
- MySQL update multiple records in a single query?
- 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?
- Can we update MySQL with if condition?
- How can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?

Advertisements