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 ...;