Mysql update set multiple rows

This article will see how to update multiple rows with different values of a MySQL table in a single query. We will be illustrating the concept with various examples.

Let us get started by making the sample data. We will be creating a table, sale_person_data, followed by inserting a few rows to it.

# create the table sale_person_data
CREATE TABLE sale_person_data (
    id INT ,
    sale_city VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    reward_title VARCHAR(255)
);
# insert the data to sale_person_data
INSERT INTO sale_person_data (id,sale_city,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"Chicago","Henry",2000,"Kitchen Essentials"),
 (2,"Chicago","Henry",5000,"Apparels"),
 (3,"New York","Henry",40,"Medicines"),
 (4,"New York","Richa",3000,"Kitchen Essentials"),
 (5,"New York","Richa",500,"Apparels"),
 (6,"New York","Richa",50,"Medicines"),
 (7,"Boston","Ved",100,"Kitchen Essentials"),
 (8,"Boston","Ved",150,"Apparels"),
 (9,"Boston","Ved",1000,"Medicines"),
 (10,"New York","George",600,"Kitchen Essentials"),
 (11,"Boston","George",1200,"Apparels"),
 (12,"Boston","George",200,"Medicines"),
 (13,"Chicago","Alexendra",487,"Kitchen Essentials"),
 (14,"Chicago","Alexendra",570,"Apparels"),
 (15,"New York","Alexendra",510,"Medicines"),
 (16,"New York","Gustav",3000,"Kitchen Essentials"),
 (17,"Chicago","Gustav",100,"Apparels"),
 (18,"New York","Gustav",150,"Medicines");

Execute select * to view the data in the sale_person_data table.

SELECT * FROM sale_person_data;

Output:-

As we can see in image1, the reward_title column is empty. 

Example1: Update multiple rows in table sale_person_data such that the reward_title column gets updated with values based on the below conditions.

Condition	reward_title value
no_products_sold > 500 and sales_department is ‘Kitchen Essentials’	‘Achiever of the Month’
no_products_sold > 500 and sales_department is ‘Apparels’	‘The Eccentric Performer’
no_products_sold > 500 and sales_department is ‘Medicines’	‘Go That Extra Mile’

Observe the below query for the solution.

UPDATE sale_person_data 
SET 
    reward_title = CASE
        WHEN
            sales_department = 'Kitchen Essentials'
                AND no_products_sold > 500
        THEN
            'Achiever of the Month'
        WHEN
            sales_department = 'Apparels'
                AND no_products_sold > 500
        THEN
            'The Eccentric Performer'
        WHEN
            sales_department = 'Medicines'
                AND no_products_sold > 500
        THEN
            'Go That Extra Mile'
    END
;

Action Output:-

Action Output in image_2 shows that 9 rows are affected. Let us now see if these 9 rows were updated by executing:

 SELECT * FROM sale_person_data;

Output:-

Output in image_3 shows that multiple rows got updated in table sale_person_data, and different values for the column reward_title are updated.

Example2: Update multiple rows in table sale_person_data such that the reward_title column gets updated only for the cities ‘Chicago’ or ‘New York’ with values based on the below conditions.

Condition	reward_title value
no_products_sold > 500 and sales_department is ‘Kitchen Essentials’	‘Achiever of the Month’
no_products_sold > 500 and sales_department is ‘Apparels’	‘The Eccentric Performer’
no_products_sold > 500 and sales_department is ‘Medicines’	‘Go That Extra Mile’

Note that we will again set the reward_title to NULL before proceeding for clarity.

UPDATE sale_person_data SET reward_title = NULL;

Observe the below query for the solution demanded by example2.

UPDATE sale_person_data 
SET 
    reward_title = CASE
        WHEN
            sales_department = 'Kitchen Essentials'
                AND no_products_sold > 500
        THEN
            'Achiever of the Month'
        WHEN
            sales_department = 'Apparels'
                AND no_products_sold > 500
        THEN
            'The Eccentric Performer'
        WHEN
            sales_department = 'Medicines'
                AND no_products_sold > 500
        THEN
            'Go That Extra Mile'
       END
WHERE sale_city IN ("Chicago", "New York");

Action Output:-

Action Output in image_4 shows that 7 rows are affected. Let us now see if these 7 rows were updated by executing:

 SELECT * FROM sale_person_data;

Output:-

Output in image_5 shows that 7 rows are updated in table sale_person_data matching the conditions mentioned above.

Example3: Update multiple rows in table sale_person_data such that city should be Santiago for sale departments ‘Kitchen Essentials’ and ‘Apparels’

Observe the below query for the solution.

UPDATE sale_person_data 
SET 
    sale_city = 'Santiago'
WHERE
    sales_department IN ('Kitchen Essentials' , 'Apparels');

Action Output:-

Action Output in image_6 shows that 12 rows are affected. Let us now see if these 12 rows were updated by executing:

 SELECT * FROM sale_person_data;

Output:-

The Output in image_7 shows that column sale_city is updated in multiple rows with where sale_department is either ‘Kitchen Essentials’ or ‘Apparels’.

READ MORE:

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