How to Update Multiple Columns in MySQL

Sometimes, database developers need to update multiple columns in MySQL. In such cases, they often use separate UPDATE statements to update each database column. But there are several more efficient ways to do it using a single SQL query. In this article, we will learn how to update multiple columns in MySQL.

Why Update Multiple Columns in MySQL

There are several significant benefits of updating multiple columns in MySQL.

1. Less Overhead – Using a single UPDATE statement is more efficient than using multiple UPDATE queries. This is because the database needs to read only a single row into memory, modify its columns and write it back once. It also reduces the number of database calls, thereby reducing network bandwidth and server processing.

2. Data Consistency – When you update multiple columns of table using single SQL query, it will ensure that all data is properly updated or fail together. This ensures data consistency and avoids a situation where only some of the columns are updated and the rest are not. In this case, the changes are atomic.

3. Better Readability – It is easier to read and understand a single UPDATE statement than go through several UPDATE statements together. It also simplifies the logic where you see all changes in one place instead of multiple changes spread across multiple places.

How to Update Multiple Columns in MySQL

Here is the basic syntax to UPDATE multiple columns using a single SQL query.

UPDATE table_name
SET column1=value1, column2=value2, column3=value3, ...
WHERE condition;

In the above column, table_name is the name of the table whose columns you want to modify. SET is the keyword used to modify the column values. It is accompanied by column name and its new value. WHERE clause is optional and used to specify one or more filtering conditions.

Let us look at some examples to update multiple columns in table.

Example 1

Here is an SQL query to update column Amount and Category in table Products where id=101.

update products
set amount=100, category='cycle'
where id=101

Example 2

You can also use functions to set the new columns values. Here is an example to set the purchase date and category using functions. We use mathematical operator ‘*’ to calculate the new value of amount column, using the value of order column. We also

update products
set amount=order*25, category=concat('blue',' ','cycle')
where id=101

Example 3

In the above example, we work with just one table. If you want to update value of one table based on the column value of another table, then here’s how to do it.

update products
set t1.amount=t2.amount, t1.category=t2.category
from products t1
inner join product_data t2 on t1.id=t2.id 
where t1.id=101

Conclusion

In this article, we have learnt several examples to update multiple columns in SQL using UPDATE statement. When you need to update multiple columns in table, you should try to do it using a single UPDATE statement. Of course, if these columns are located in different unrelated tables, then you may need to use multiple UPDATE statements.

Also read:
How to Speed Up SQL Queries
How to Get Row Number in PostgreSQL
How to Delete Duplicate Rows in MySQL