How To Update Multiple Columns in MySQL?

In this article, we will be learning about how one can easily update multiple columns in MySQL. There are scenarios where we don't want to create a new table every time we receive an update for the existing primary key, and we look for ways to update multiple columns in MySQL with a single query.

With the help of the UPDATE statement, the user can update the data that justifies the consideration in a table. The value could be modified in more than one column for a single row or multiple rows.

Two modifiers are primarily supported in the UPDATE statement for updating multiple columns in MySQL.

• LOW_PRIORITY: With the LOW_PRIORITY modifier, one can let the UPDATE statement know that it wants to delay the update until there is no such connection for reading the data from the table. It takes effect for the storage engines utilized for only table-level locking, including MyISAM, MERGE, and MEMORY.
• IGNORE: With the IGNORE modifier, one can ensure that the UPDATE statement shall hold TRUE and continue to update the rows even when there is any error seen. No updates occur for the rows that cause errors like duplicate-key conflicts.

Syntax

Let us learn about the syntax to update multiple columns in MySQL.

Parameters:

• Start by defining the name of the table in the table_name section to let the code know that it is the table for which the data needs to be updated after the UPDATE keyword.
• User can also implement query modifiers like the WHERE clause in the above syntax for updating multiple columns in MySQL. This is optional, but one can use it to enhance the results and make them more accurate and according to the user's requirements.
• Define the column that you need to update to add the new value in the SET clause seen above. It is this section that can be further utilized for updating the values for multiple columns. Users can implement this by utilizing a list of comma-separated assignments. Here, the value in each column's assignment can be supplied in the form of an expression, a literal value, or a subquery.
• The rows where the updation must happen could also be defined by specifying the condition in the WHERE clause. This is optional, but one can leverage it for specific updation scenarios. Also, it must be noted that if this WHERE clause is skipped, all the rows in the table are bound to be modified.

QuickNote: Though the WHERE clause is optional, one must not forget its importance. For scenarios where you want to update only one row, the user can leverage this clause. However, if you forget to implement the WHERE clause, it shall then accidentally also update all rows in the table.

MySQL Update Example

In this section of the article, we shall be covering the example of how one can update multiple columns in MySQL.

Before we start to update multiple columns in MySQL, let us create a table named "School" with few records, as seen in the code below.

Code:

Once you execute the SELECT statement, you shall see five rows are inserted as seen below:

Output:

Explanation: We will have four columns in the table School. Here, we have specified the Students_Name with the UNIQUE constraint to make sure that no value is duplicated in this column. Then we need to have a certain number of records in the table. We used the INSERT code to do so. Here we inserted five rows into the School table. To validate if the INSERT statement has fed the information in the School table, we make use of the SELECT statement, as seen in the code above.

Example 1: Update Multiple Columns in MySQL

Now, let us see how one can update multiple columns in MySQL using the syntax we learned above.

Code:

Once you execute the SELECT statement, you shall see the row with Class_ID as 39 is updated, as seen below:

Explanation: We will have five columns in the table School. Here, the rows associated with the Class_ID as 39 gets updated. We see to update multiple columns in MySQL, and the condition was set, which helped the table identify the row where the modification was needed. Also, both the columns Subjects and Class gets updated for Class_ID set as 39. To validate if the update of multiple columns in MySQL has happened in the School table, we make use of the SELECT statement, as seen in the code above.

Example 2: Update Uingle Columns in MySQL

Now, let us see how one can update single columns in MySQL using the syntax we learned above.

Code:

Once you execute the SELECT statement, you shall see the row with Class_ID as 36 is updated, as seen below:

Explanation: We will have five columns in the table School. Here, the rows associated with the Class_ID as 36 gets updated. We see to update single columns in MySQL, and the condition was set, which helped the table identify the row where the modification was needed. Also, the column Subjects gets updated for Class_ID set as 36. To validate if the update of multiple columns in MySQL has happened in the School table, we make use of the SELECT statement, as seen in the code above.

Example 3: Update Multiple Columns in MySQL Using REPLACE String

Now, let us see how one can update multiple columns in MySQL using the REPLACE string.

Code:

Once you execute the SELECT statement, you shall see the row with Class_ID as 36 is updated, as seen below:

Explanation: We will have five columns in the table School. Here, the rows associated with the Class_ID as 33 gets updated. We see to update multiple columns in MySQL, and the condition was set, which helped the table identify the row where the modification was needed. Also, the columns Subjects and Class gets updated for Class_ID set as 33 via the help of the REPLACE string. To validate if the update of multiple columns in MySQL has happened in the School table, we make use of the SELECT statement, as seen in the code above.