How to update multiple columns in MySQL

Posted on Oct 28, 2021

Learn how to update multiple columns in MySQL database server using a single UPDATE statement

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.

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.