bulk updating a list of values from a list of ids

问题:

I'm frequently facing this issue, as an Oracle user, playing around with MySql.

Be the following situation:

• a list of ids (1, 2, 3, ..., n)
• a list of values ('val1', 'val2', 'val3', ..., 'valn') [The values are obviously totally different than these]
• The 2 previous lists are passed ordered. It means the value passed first corresponds to the id passed first.

The objective is to update all the value of the table value having the corresponding id: val1 should update id 1, val2 should update id 2 etc... In only ONE query.

The easy solution is to update n times:

UPDATE `value` SET `value`='val1' WHERE id = 1;
UPDATE `value` SET `value`='val2' WHERE id = 2;
UPDATE `value` SET `value`='val3' WHERE id = 3;
...
UPDATE `value` SET `value`='valn' WHERE id = n;

But I would love to bulk update all this.

sqlfiddle to play a bit: http://sqlfiddle.com/#!2/d02e8

回答1:

Here is one way to do this using one query. It won't be the prettiest-formatted query, but it will be just one.

<?php

$id_list = implode(',', $ids);
$whens = implode(
    "\n    ",
    array_map(
        function ($id, $value) {
            return "WHEN {$id} THEN {$value}";
        },
        $ids,
        $values
    )
);

$sql = "
    UPDATE value
    SET value = CASE id
    {$whens}
    END
    WHERE id IN ({$id_list})
";
?>

See my modified SQLFiddle.