How to update multiple columns in MySQL

The MySQL UPDATE statement can be used to update multiple columns at once.

To do so, you need to specify the columns and the new values that you want to update next to the SET clause.

The syntax would look as follows:

UPDATE table_name 
  SET column_1 = value_1,
      column_2 = value_2,
      column_3 = value_3,
      ...
  WHERE ...

For example, suppose you have a table called pets with the following data:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Peter  | dog     |    5 |
+----+--------+---------+------+

Here’s an example UPDATE statement to update multiple columns:

UPDATE pets
  SET `owner` = "Ronald",
      `species` = "cat",
      `age` = 1
  WHERE id = 5;

Once executed, then the data on the row with id = 5 will be updated as shown below:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Ronald | cat     |    1 |
+----+--------+---------+------+

And that’s how you can update multiple columns in MySQL.

Don’t forget to specify the WHERE clause or you might encounter the ERROR code 1175 when running the UPDATE statement.

More MySQL tutorials