SQL：使用单个查询更新具有多个值的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4690668/
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
SQL: Update a column with multiple values with single query
提问by Sandeep Jindal
I have an update query like following:
我有一个更新查询，如下所示：
update table TABLE1 set COL1 = 'X' where COL2 = 'Y' ---1
Support the values 'X' and 'Y' are fetched from database now TABLE2. E.g.
现在支持从数据库 TABLE2 中获取值 'X' 和 'Y'。例如
select COL1, COL2 from TABLE2. ----2
I want to update table TABLE1 with values from TABLE2.
我想用 TABLE2 中的值更新表 TABLE1。
Just to make it more clear, assume that TABLE2 has following values:
为了更清楚，假设 TABLE2 具有以下值：
Can you please help me in doing this in a single query!
你能帮我在一个查询中做到这一点吗！
I am using Oracle 11g.
我正在使用 Oracle 11g。
回答by Dave Costa
For Oracle, this is the most basic way to do it:
对于 Oracle，这是最基本的方法：
update TABLE1
set COL1 = (select TABLE2.COL1 from TABLE2 where TABLE2.COL2 = TABLE1.COL2)
where COL2 IN (select TABLE2.COL2 from TABLE2);
This can be inefficient in some cases since it could execute a subquery for every row in TABLE1.
在某些情况下，这可能是低效的，因为它可以为 TABLE1 中的每一行执行子查询。
Depending on the declaration of primary key or unique constraints on both tables, you may be able to use the updateable inline-view method, which is probably more efficient:
根据两个表上主键或唯一约束的声明，您可以使用 updateable inline-view 方法，这可能更有效：
update
(select TABLE1.COL1 as T1C1, TABLE1.COL2 as T1C2, TABLE2.COL1 as T2C1
from TABLE1 join TABLE2 on TABLE2.COL2 = TABLE1.COL2
)
set T1C1 = T2C1;
回答by Allan
@Dave Costa's answer is correct, if you limit yourself to updatestatements. However, I've found that using a mergestatement in these situations allows me to do this in a more straightforward manner:
@Dave Costa 的回答是正确的，如果你只限于update陈述的话。但是，我发现merge在这些情况下使用语句可以让我以更直接的方式执行此操作：
merge into TABLE1
using TABLE2
on (TABLE2.COL2 = TABLE1.COL2)
when matched then
update set TABLE1.COL1 = TABLE2.COL1;
回答by Rockcoder
update TABLE1
set TABLE1.COL1 = TABLE2.COL1
from TABLE1
join TABLE2 on TABLE1.COL2 = TABLE2.COL2
(this would work on Sql Server)
（这适用于 Sql Server）
回答by Michael Pakhantsov
for oracle:
甲骨文：
UPDATE Table1 t1
SET (X,Y) = (SELECT X,Y from Table2 WHERE ...YourConditions...)
WHERE ... Another Conditions ...
for mysql, sql-server
对于 mysql, sql-server
UPDATE t1
SET t1.X = t2, t2.Y = t2.Y
FROM Table1 t1, Table2 t2
WHERE t1.Something = t2.Something
