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:
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:
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:
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:
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:
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:
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!