从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屋!