How to Update Multiple Columns in MySQL

In MySQL, the UPDATE statement lets you change one or more values in your column. It could be that something changed in a given value that is stored in your table, and you must change it. In that case, using the UPDATE statement is the way to go.

While we are used to updating one column at a time, how about updating multiple columns with a single UPDATE statement? If that’s what you aim to achieve, this post discusses the step-by-step guide that you should follow to update multiple columns in MySQL. Read on!

Update Multiple Columns in MySQL

Updating multiple columns in MySQL takes the same path as updating a single column. The only difference is that you must specify the columns that you want to update in your SET command. The following is the syntax to follow:

UPDATE table_name SET column1 – value1, column2 = value2, column_n = value_n WHERE [condition];

Let’s create a table that we use to demonstrate how the update of multiple columns in MySQL works. We name our “multiple” table and have it in our “student” database.

Let’s quickly insert the values into the table.

If we select all from our table, we can see that we have five rows inserted:

Let’s work with the five rows to see how we can update multiple columns. Let’s cover a few examples.

In the first example, let’s say we want to change the name and course columns in the row whose id is 2. For that, we have our command as follows:

UPDATE multiple SET name=’linuxhint’, course=’MySQL’ WHERE id=2;

After executing the command, we get an output which shows that one row is affected:

If we list the contents of our table and check the row whose id=2, we can confirm that we managed to update the two columns with our “update” command.

That’s our first example on how to update multiple columns in MySQL.

Let’s have another example where we want to update our table’s name and age columns. We take a similar approach and ensure that the new values that we add match the datatype for the particular row. Let’s update the columns where the course is “MST” with the following command:

UPDATE multiple SET name=’Nelly’, age=33 WHERE course=’MST’;

We can confirm that we updated the name and age columns for the specified course.

It’s also possible to update all the columns for a particular row excluding the primary key since it is set to autoincrement. Let’s say we want to update the last row in our table and have new values for all its columns.

We execute our command as follows:

UPDATE multiple SET name=’last’, age=42, course=’Linux’ WHERE id=5;

If we inspect our table, we see that we successfully updated three columns with a single UPDATE statement.

It’s also possible to replace the strings in multiple columns in your MySQL table with a single UPDATE statement. Let’s have a case where we want to replace the values for the course and age columns where the id =5. For that, we have our UPDATE statement written as follows:

UPDATE multiple SET course=REPLACE(course, ‘Linux’, ‘Linuxhint’), age=REPLACE(age, 29, 35) WHERE id=5;

Once we execute the command, the UPDATE query replaces the values for the specified columns to match those that you included in the query. That’s another way of updating multiple columns in MySQL.

Our update query is successful as evident in the following image:

Conclusion

When working with MySQL, it’s possible to update different columns of your table. You can set the columns and the new value within one update statement. Still, you can use the “replace” option and specify the column to replace the current value and what new value to replace it. Both options are covered in this post. Try them out!

How to Update Multiple Columns in MySQL

Update Multiple Columns in MySQL

Conclusion

About the author

Denis Kariuki