How To Update Multiple Columns in MySQL

Last updated on August 12th, 2024 at 06:57 am

MySQL database allows you to update column values using UPDATE…SET statement. Sometimes you may need to update multiple columns in MySQL. You can also do this with SET statement. In this article we will look at how to update multiple columns in MySQL with single query.

How To Update Multiple Columns in MySQL

Here are the steps to update multiple columns in MySQL.

UPDATE statement allows you to update one or more values in MySQL. Here is the syntax to update multiple values at once using UPDATE statement.

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET
column_name1 = expr1,
column_name2 = expr2,
…
[WHERE condition];

In the above statement, you need to specify the table_name, then mention the columns you want to update, along with their new values, one after the other, separated by commas. Finally, you need to optionally specify a WHERE clause to filter the rows you want to update.

Also Read : How to Escape Single Quote, Special Characters in MySQL

Update Multiple Columns in One Table

In this case, we will learn how to update multiple columns in a single table. Let us say you have the following table employees(id, first_name, last_name)

mysql> create table employees(
       id int primary key, 
       first_name varchar(255),
       last_name varchar(255)
       );

mysql> insert into employees(id, first_name, last_name)
       values(1,'John','Doe'),
       (2,'Jane','Doe');

mysql> select * from employees;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

Here’s the SQL query to update multiple columns first_name and last_name in single query.

mysql> update employees
       set  first_name='Jim',
       last_name='Don'
       where id=1;

mysql> select * from employees;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | Jim        | Don       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

As you can see, both first_name and last_name columns have been updated at once.

Update Multiple Columns in Multiple Tables

In this case, we will learn how to update multiple columns in multiple tables. This statement is a little different from the standard UPDATE…SET statements. You need to include a JOIN clause in the UPDATE part of your statement, as shown below. Let us say you also have another table sales(employee_id, order_id, amount), in addition to the employees table that you have described above.

mysql> CREATE TABLE sales (
    employee_id int,
    order_id int,
    amount int,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

mysql> insert into sales(employee_id, order_id, amount) 
       values(1, 1, 100),
             (1,3,250);

mysql> select * from sales;
+-------------+----------+--------+
| employee_id | order_id | amount |
+-------------+----------+--------+
|           1 |        1 |    100 |
|           1 |        3 |    250 |
+-------------+----------+--------+

Here is an SQL query to update first_name column in employees table as well as amount column in sales table using a single UPDATE statement.

mysql> UPDATE employees
       JOIN sales ON employees.id = sales.employee_id
       SET first_name = 'Tim', amount = 350
       WHERE employees.id = 1;

mysql> select * from employees;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Tim        | Doe       |
|  2 | Jane       | Doe       |
+----+------------+-----------+

mysql> select * from sales;
+-------------+----------+--------+
| employee_id | order_id | amount |
+-------------+----------+--------+
|           1 |        1 |    350 |
|           1 |        3 |    350 |
+-------------+----------+--------+

In the above UPDATE statement, we use a JOIN to link both sales and employees tables. Once we do this, we have access to all columns of both tables and we are free to update one or more columns in both tables.

Similarly, you can update multiple columns in multiple tables.

Conclusion

In this article, we have learnt how to update multiple columns in MySQL. If you are updating them in a single table, you can easily do this using multiple ‘column=expression’ in SET statement. If you want to update them from multiple tables, then you need to join them in UPDATE clause of your statement while you include multiple ‘column=expression’ in SET clause of statement.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.