Mysql update set multiple columns

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

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