使用一个 SQL 查询更新多行上的单列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36872053/
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 a single column on multiple rows with one SQL query
提问by Steven Rogers
I need to update a single column over a thousand rows in the database. Normally when I need to do this, I'll do the following:
我需要更新数据库中超过一千行的单列。通常，当我需要这样做时，我会执行以下操作：
UPDATE [table] SET DATA="FOO" WHERE ID=23;
UPDATE [table] SET DATA="ASD" WHERE ID=47;
UPDATE [table] SET DATA="FGH" WHERE ID=83;
UPDATE [table] SET DATA="JKL" WHERE ID=88;
UPDATE [table] SET DATA="QWE" WHERE ID=92;
UPDATE [table] SET DATA="BAR" WHERE ID=97;
I feel like there should be a way to do this easily, but after searching around online, I cannot find a solution.
我觉得应该有一种方法可以轻松做到这一点，但是在网上搜索后，我找不到解决方案。
What I was hoping for was this:
我所希望的是：
-- THIS DOES NOT WORK
UPDATE [table]
SET DATA=("FOO", "ASD", "FGH", "JKL", "QWE", "BAR")
WHERE ID=(23, 47, 83, 88, 92, 9);
An important piece of information to note is that I am always setting one column, the same column across a specific set of rows.
需要注意的一条重要信息是，我总是设置一列，即跨一组特定行的同一列。
Does anyone know the best way to do this?
有谁知道最好的方法来做到这一点？
回答by JNevill
I'm assuming that you are in MySQL. You can use a combination of "Field" and "Elt" functions to do what you need in a single query (beside the CASE WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN ENDor IF(<condition>,<output>,if(<condition2>,<output2>, if()))methods.
我假设你在 MySQL 中。您可以使用“Field”和“Elt”函数的组合在单个查询中执行您需要的操作（除了CASE WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN WHEN THEN ENDorIF(<condition>,<output>,if(<condition2>,<output2>, if()))方法。
UPDATE [table] SET DATA=ELT(FIELD(ID,
13, 14, 15, 16, 17, 18, 19),'FOO', 'ASD', 'FGH', 'JKL', 'QWE', 'BAR');
This is similar to the DECODE()function in Oracle, which I wish had counterparts in other DBMS's.
这类似于DECODE()Oracle 中的功能，我希望在其他 DBMS 中也有对应的功能。
回答by trincot
You could use the MERGEstatement which is in the SQL:2003standard and available in Transact-SQL since SQL Server 2008:
您可以使用SQL:2003标准中的MERGE语句，并且自 SQL Server 2008 起在 Transact-SQL 中可用：
MERGE mytable
USING (VALUES (23, 'FOD'),
(47, 'ASD'),
(83, 'FGH'),
(88, 'JKL'),
(92, 'QWE'),
( 9, 'BAR')) AS pairs(id2, data2)
ON id = id2
WHEN MATCHED
THEN UPDATE SET data = data2
The USINGclause allows to specify a derived table using a table value constructor(See example under point D on that page).
该USING子句允许使用表值构造函数指定派生表（请参阅该页面上 D 点下的示例）。
Alternatively, the more commonly implemented SQL:92 standardsyntax to do this would be:
或者，更常用的SQL:92 标准语法是：
UPDATE mytable
SET data =
CASE id
WHEN 23 THEN 'FOD'
WHEN 47 THEN 'ASD'
WHEN 83 THEN 'FGH'
WHEN 88 THEN 'JKL'
WHEN 92 THEN 'QWE'
WHEN 9 THEN 'BAR'
END
WHERE id IN (23, 47, 83, 88, 92, 9);
The obvious downside is that you end up specifying the id values twice. You could do without the WHEREclause and add ELSE datain the CASEconstruct, but then you would actually update all rows, which is inefficient and may have undesired side-effects (via triggers).
明显的缺点是您最终会指定两次 id 值。您可以不使用该WHERE子句并添加ELSE data到CASE构造中，但是您实际上会更新所有行，这是低效的并且可能会产生不希望的副作用（通过触发器）。
回答by Andrews B Anthony
You can actually do it using insert into ...on duplicate key update
您实际上可以使用insert into ...on 重复密钥更新来做到这一点
insert into [table](ID,DATA)
values(23,'FOO'),(47,'ASD'),(54,'DSF')..,
on duplicate key update DATA=values(DATA)
回答by TheGameiswar
one way i could think of is join..The way you are trying to update wont scale
我能想到的一种方法是加入..您尝试更新的方式不会扩展
---insert your updates in a table like below every time you want to update data.
---每次要更新数据时，将更新插入如下表中。
create table #test
(
data varchar(2),
id int
)
insert into #test
select 'abc',1
union all
select 'cde',2
now you update your main table like below
现在你更新你的主表，如下所示
update mt
set mt.data=t.data
from maintable mt
join
#test t
on t.id=mt.id
回答by granadaCoder
Sql Server
数据库服务器
UPDATE t1
set DATA = derived1.NewValue
From
[table] t1 join
(
Select 'Foo' as NewValue, 23 as MyId
union all Select 'ASD' as NewValue, 47 as MyId
union all Select 'FGH' as NewValue, 83 as MyId
) as derived1
on t1.ID = derived1.MyId
