oracle 如何在Oracle SQL中使用相同的子查询更新同一个表中的多个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30081395/
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
How to update multiple columns in the same table with the same sub-query in Oracle SQL
提问by rghome
Is there a better way of writing this general type update in Oracle:
有没有更好的方法在 Oracle 中编写这种通用类型更新：
UPDATE table1
SET c1 = ( SELECT d1 FROM table2 WHERE table1.id = table2.id ),
c2 = ( SELECT d2 FROM table2 WHERE table1.id = table2.id )
The update above is only an example. The sub-select could be much more complex.
上面的更新只是一个例子。子选择可能要复杂得多。
I see other SQL dialects have UPDATE ... SET ... FROM, but this does not seem to be in Oracle.
我看到其他 SQL 方言有 UPDATE ... SET ... FROM，但这似乎不在 Oracle 中。
回答by Boneist
You can update multiple columns in one go:
您可以一次性更新多个列：
drop table t1;
drop table t2;
create table t1 (col1 number, col2 number, col3 number);
create table t2 (col1 number, col2 number, col3 number);
insert into t1 values (1, 10, 100);
insert into t1 values (2, 20, 200);
insert into t2 values (1, 100, 1000);
insert into t2 values (2, 200, 2000);
commit;
update t1
set (col2, col3) = (select col2, col3 from t2 where t2.col1 = t1.col1);
commit;
select * from t1;
COL1 COL2 COL3
---------- ---------- ----------
1 100 1000
2 200 2000
