在 MySQL 中更新 1 列中的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9346755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple rows in 1 column in MySQL
提问by Don P
What is the proper query for updating multiple rows in MySQL at the same time?
同时更新 MySQL 中的多行的正确查询是什么？
I am only updating 1 column:
我只更新 1 列：
UPDATE example_table SET variable1 = 12 WHERE id=1;
UPDATE example_table SET variable1 = 42 WHERE id=2;
UPDATE example_table SET variable1 = 32 WHERE id=3;
UPDATE example_table SET variable1 = 51 WHERE id=4;
This seems like it may be inefficient, or if it is the most efficient query let me know :)
这似乎效率低下，或者如果它是最有效的查询，请告诉我:)
回答by Vikram
you can use cases like below:
您可以使用以下案例：
UPDATE example_table
SET variable1 = CASE id
WHEN 1 THEN 12
WHEN 2 THEN 42
WHEN 3 THEN 32
WHEN 4 THEN 51
END
WHERE id BETWEEN 1 AND 4
回答by No'am Newman
Not applicable to your example, but you probably will find this useful:
不适用于您的示例，但您可能会发现这很有用：
UPDATE table
SET value = <value>
WHERE field = <specific value>
This way you can update one field in a table on the basis of another field in the same table. All the applicable rows will be updated. To quote an example which I used at work earlier this morning
通过这种方式，您可以根据同一表中的另一个字段更新表中的一个字段。所有适用的行都将被更新。引用我今天早上在工作中使用的一个例子
UPDATE porderitems
SET currency = -2
WHERE ord = 40396
This query update the porderitems table (purchase order lines), setting the currency to -2 for all the lines connected to purchase order 40396. The query neither knows nor cares how many lines there are in that purchase order; all of them will be updated.
此查询更新 porderitems 表（采购订单行），将所有连接到采购订单 40396 的行的货币设置为 -2。该查询既不知道也不关心该采购订单中有多少行；所有这些都将被更新。
回答by Sk Arka
UPDATE personal_detailsSET country_id= 6 where id between 26 and 40. I think this code would work if the new value is same and it needs to update in multiple rows.
UPDATE personal_detailsSET country_id= 6 其中 id 介于 26 和 40 之间。我认为如果新值相同并且需要在多行中更新，则此代码将起作用。
回答by J Cooper
if your values are from another table:
如果您的值来自另一个表：
UPDATE example_table
JOIN values_table ON values_table.id = example_table.id
SET example_table.variable1 = values_table.value
