MySQL UPDATE Bulk UPDATEUPDATE people SET name = (CASE id WHEN 1 THEN 'Karl' WHEN 2 THEN 'Tom' WHEN 3 THEN 'Mary' END) WHERE id IN (1,2,3); By bulk updating only one query can be sent to the server instead of one query for each row to update.
The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
The easiest solution in your case is to use ON DUPLICATE KEY UPDATE
construction. It works really fast, and does the job in easy way.
INSERT into `table` (id, fruit)
VALUES (1, 'apple'), (2, 'orange'), (3, 'peach')
ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);
or to use CASE
construction
UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
WHEN 2 THEN 'val2'
WHEN 3 THEN 'val3'
END)
WHERE column1 IN(1, 2 ,3);
If the "bulk" data you have is dynamic and is coming from PHP (you did tag it, after all), then the query would look something like this:
INSERT INTO `foo` (id, bar)
VALUES
(1, 'pineapple'),
(2, 'asian pear'),
(5, 'peach')
ON DUPLICATE KEY UPDATE bar = VALUES(bar);
and the PHP to generate this from an existing array (assuming the array is of a format like:
$array = (
somevalues_key => othervalues_value
);
) would look something like this (by no means the best (doesn't address escaping or sanitizing the values, for instance), just an quick example):
$pairs = array();
foreach ($array as $key => $value) {
$pairs[] = "($key, '$value')";
}
$query = "INSERT INTO `foo` (id, bar) VALUES " . implode(', ', $pairs) . " ON DUPLICATE KEY UPDATE bar = VALUES(bar)";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With