Update Column Values on Multiple Rows in MySQL

Q

How To Update Column Values on Multiple Rows in MySQL?

✍: FYIcenter.com

A

If the WHERE clause in an UPDATE matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example:

mysql> UPDATE fyi_links SET counts = 9, notes = 'Wrong' 
   WHERE id >= 500;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links WHERE id >= 500;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 601 | moc.retneciyf.ved | Wrong |      9 | 2006-04-30    |
| 602 | moc.retneciyf.abd | Wrong |      9 | 2006-08-31    |
| 603 | moc.retneciyf.aqs | Wrong |      9 | 2006-08-31    |
| 610 |                   | Wrong |      9 | 2006-08-31    |
| 500 | moc.retneciyf.www | Wrong |      9 | 2006-08-31    |
+-----+-------------------+-------+--------+---------------+
5 rows in set (0.00 sec)

This statement updated 5 rows with the same new values on all 5 rows.

 

INSERT, UPDATE and DELETE Statements in MySQL

⇒⇒MySQL Database Tutorials

2016-10-17, 187👍, 0💬