Bulk update mysql with where statement

Question

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

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

with somevalues like :

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

and othervalues :

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

maybe it's impossible with mysql ? a php script ?

Answer 1

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

Answer 2

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

Answer 3

You could try an UPDATE with JOIN as below:

UPDATE table
INNER JOIN (
            SELECT 1 column1, 2 column2, 10 new_v1, 20 new_v2, 30 new_v3
  UNION ALL SELECT 4 column1, 5 column2, 40 new_v1, 50 new_v2, 60 new_v3
) updates
  ON table.column1 = updates.column1
  AND table.column2 = updates.column2
SET
  table.column1 = updates.new_v1,
  table.column2 = updates.new_v2,
  table.column3 = updates.new_v3;

As long as you can craft the inner SELECT statements from the updates subquery you would get the benefit of running all these updates in a single statement (which should give you some performance boost on InnoDB depending on your table size).

Answer 4

If you are using a drag & drop tableView or collectionView to sort datas in your app, like allowing users to arrange their photos by drag and drop functionality, send a comma seperated list of ordered ids to the backend after user edits finish.

In your backend, explode ids to the an array like

$new_ranks = array();
$supplied_orders = explode(",", $_POST["supplied_new_order"]); //52,11,6,54,2 etc
$start_order = 99999;
foreach ($supplied_orders as $supplied_row_id) {
    //your all validations... make sure supplied_row_id belongs to that user or not etc..

    $new_ranks[intval($supplied_row_id)] = $start_order--;
}

now, you can update all new ranks like @Farside recommendation 2.

if (count($new_ranks) > 0) {
    $case_sqls = array(); 
    foreach ($new_ranks as $id => $rank) {
        $case_sqls[] = "WHEN ".intval($id)." THEN ".intval($rank)."";
    } 
    $case_sql = implode(" ", $case_sqls);

    $this->db->query("
        UPDATE 
            service_user_medias
        SET 
            rank = (CASE id ".$case_sql." END)
        WHERE 
            id IN(".implode(",", array_keys($new_ranks)).");
    ");
}

Answer 5

If you have data in array format then try this

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

then set it like below

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

hope it'll work.

Reference from this.