Update multiple rows in MySQL

MySQL support bulk inserts, which means that you can insert into a table multiple rows within a single SQL query. All you need is just to provide a comma-separated list of rows:

insert into user (name, age) values ('Sam', 25), ('Mary', 18), ('Tiffany', 19);

However bulk updates aren't supported directly within update syntax. Still a bulk update could be implemented with an additional table and inner join trick.

Assume you have a table employee with three fields:

Table employee
------------------
id     int
name   varchar(50)
salary int

A company hired a new boss and he decided to review employees salary. You have a new list of values and need to update them in a single MySQL query.

Fist, create new empty table with the same schema:

create table employee_bulk
    select * from employee limit 0;

Second, insert into this table your new values:

insert into employee_bulk (id, name, salary)
    values (1, 'George', 1500), (2, 'April', 1900), (3, 'Boris', 1750);

Next and the last, perform bulk MySQL update with inner join trick:

update employee
    inner join employee_bulk on employee_bulk.id = employee.id
    set employee.salary = employee_bulk.salary;

You may pack everything up into a single procedure to make this operation more convenient:

truncate employee_bulk;
insert into employee_bulk (id, name, salary) values ...;
update employee inner join employee_bulk on employee_bulk.id = employee.id set ...;