从ID列表批量更新值列表 [英] bulk updating a list of values from a list of ids

问题描述

作为Oracle用户，我经常遇到这个问题，玩转MySql.

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

处于以下情况:

• 一个ID列表(1、2、3，...，n)
• 值列表('val1'，'val2'，'val3'，...，'valn')[这些值显然完全不同]
• 前两个列表按顺序传递.这意味着首先传递的值对应于首先传递的id.

目标是更新表value中具有相应id的所有表value:val1应该更新id 1，val2应该更新id 2，等等……仅在一个查询中.

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.

简单的解决方案是更新n次:

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播放一下: http://sqlfiddle.com/#!2/d02e8

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

推荐答案

这是使用 one 查询执行此操作的一种方法.它不会是最漂亮的格式的查询，但只会是一个.

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

请参阅我修改过的 SQLFiddle .

这篇关于从ID列表批量更新值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！