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

image1: sale_person_data

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.

Conditionreward_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’
table1

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

image_2

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

image_3

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.

Conditionreward_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’
table1

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

image_4

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

image_5

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

image_6

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

image_7

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:

We hope this article helped you with updating multiple rows in a single MySQL query. Good Luck!!!