This article will see how to update multiple columns of a MySQL table in a single query. We will be illustrating the concept with various examples.
Table of Contents:-
- MySQL update set multiple columns for a single row
- MySQL update set multiple columns for multiple rows
Let us get started by making the sample data. We will be creating a table, employee_details, followed by inserting a few rows to it.
# create the table employee_details CREATE TABLE employee_details( emp_id int NOT NULL AUTO_INCREMENT, emp_firstName varchar(255) DEFAULT NULL, emp_lastName varchar(255) DEFAULT NULL, emp_designation varchar(255) DEFAULT NULL, emp_city varchar(255) DEFAULT NULL, primary key(emp_id) ); # insert rows to the table employee_details INSERT INTO employee_details (emp_id,emp_firstName,emp_lastName,emp_designation) VALUES(1,"Henry","Smith","Assistant Manager"), (2,"Richa","Johnson","Executive"), (3,"Veronica","Brown","Executive"), (4,"George","Jones","Executive"), (5,"Harsh","Garcia","Assistant Manager"), (6,"William","Jones","Assistant Manager"), (7,"Rebecca","Miller","Senior Manager"), (8,"Paul","Davis","Assistant Manager"), (9,"Pualou","Miller","Executive");
Execute select * to view the data in the employee_details table.
SELECT * FROM employee_details;
Output:-

MySQL update set multiple columns for a single row
Let us look into the example, but we will peek into the syntax before moving ahead.
Syntax:-
Frequently Asked:
- MySQL Select Multiple Values
- MYSQL INSERT INTO
- Error Code: 1364 [Solved] Field doesn’t have a default value
- MySQL check if table exists
UPDATE name_of_your_table SET column1 = value1, column2 = value2, ...... WHERE conditions;
- name_of_your_table:Â is the table that we will update.
- column1 = value1, column2 = value2, ……: is the list of columns with individual values to be updated.
- conditions: the condition should include the unique key or primary key or any unique requirement.
Example: Update table employee_details such that emp_designation updates to ‘Assistant Sales Manager’ and emp_city updates to ‘Santiago’ for the employee with emp_id = 5
Observe the below query for the solution.
UPDATE employee_details SET emp_designation = 'Assistant Sales Manager', emp_city = 'Santiago' WHERE emp_id = 5;
Action Output:-

Action Output in image_2 shows that 1 row is affected. Let us now see the changes by executing:
SELECT * FROM sale_person_data where emp_id = 5;
Output:-

MySQL update set multiple columns for multiple rows
Let us look into the example, but we will peek into the syntax before moving ahead.
Syntax:-
UPDATE name_of_your_table SET column1 = value1, column2 = value2, ...... WHERE conditions;
- name_of_your_table:Â is the table that you will update.
- column1 = value1, column2 = value2, ……: is the list of columns with individual value to be updated.
- conditions:Â conditions that fulfill multiple rows.
Example: Update table employee_details such that emp_designation updates to ‘Assistant Sales Manager’ and emp_city updates to ‘Chicago’ for all employees with designation ‘Assistant Manager’
Observe the below query for the solution.
UPDATE employee_details SET emp_designation = 'Assistant Sales Manager', employee_city = 'Chicago' WHERE emp_designation = 'Assistant Manager';
Action Output:-

Action Output in image_4 shows that 3 rows are affected. Let us now see the changes by executing:
SELECT * FROM sale_person_data;
Output:-

READ MORE:
- Mysql update set multiple rows
- Mysql update column with value from another table
- MySQL Update with Inner Join
- MySQL Insert with Join
- MySQL: Insert in a loop
We hope this article helped you with updating multiple rows in a single MySQL query. Good Luck!!!