Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Bulk update mysql with where statement

People also ask

How do I UPDATE a bulk record in MySQL?

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.

Can we use UPDATE with WHERE clause?

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