Bulk update mysql with where statement

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)";

Bulk update mysql with where statement

Related

Recent Posts