Mysql bulk update with where

Question

How to update mysql data in bulk? How to define something like this:

UPDATE `table` WHERE `column1` = somevalues SET `column2` = othervalues 

with some meanings such as:

 VALUES ('160009'), ('160010'), ('160011'); 

and other values:

 VALUES ('val1'), ('val2'), ('val3'); 

Perhaps this is not possible with mysql? php script?

Answer 1

The simplest solution in your case is to use the ON DUPLICATE KEY UPDATE construct. It works very fast and makes the job easy.

 INSERT into 'table' (id, fruit) VALUES (1, 'apple'), (2, 'orange'), (3, 'peach') ON DUPLICATE KEY UPDATE fruit = VALUES(fruit); 

or use the CASE construct

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

Answer 2

If the "bulk" data you have is dynamic and comes from PHP (you still marked it), the query will 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 PHP to generate this from an existing array (assuming the array has the format:

 $array = ( somevalues_key => othervalues_value ); 

) will look something like this (by no means the best (does not apply to escaping or disinfecting values, for example), just a 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)"; 

Answer 3

If you have data in array format try this

and your query is like "UPDATE table WHERE column1 = ? SET column2 = ?"

then install it as below

 foreach($data as $key => $value) { $query->bind_param('ss', $key, $value); $query->execute(); } 

hope it works.

Link out of this .

Answer 4

You can use implode to bulk mysql update with where statement

 $requete = $pdo->prepare("update table_name set field = 1 where id in(".implode(', ', $ids).")"); $requete->execute(); 

$ ids is an array