Oracle 更新多个表中的多个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16522218/
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
Oracle update multiple columns in multiple tables
提问by KS1
Need a bit of help on a query for Oracle. Basically I would like to put together a query that updates a number of columns to replace actual data with test data. To do this we need to do things like change project codes in two tables, but the references must match, so for example I'm looking to do...
需要一些有关 Oracle 查询的帮助。基本上，我想将一个查询放在一起，该查询更新多个列以用测试数据替换实际数据。为此，我们需要执行诸如更改两个表中的项目代码之类的操作，但引用必须匹配，因此例如我希望执行...
UPDATE table1 t1, table2 t2
SET t1.project_ref = concat('projectRef-',rownum),
t2.project_ref = t1.project_ref
WHERE t1.project_ref = t2.project_ref and t1.project_client = XYZ
This would change t1.project_ref and t2.project_ref to something like 'projectRef-1' for client XYZ.
这会将 t1.project_ref 和 t2.project_ref 更改为类似于客户端 XYZ 的“projectRef-1”。
Any ideas?
有任何想法吗？
Regards
问候
KS
KS
回答by bitmagier
Generally updates on joins are possible in Oracle.
通常，在 Oracle 中可以对连接进行更新。
This is the syntax: update (select T1.* from T1, T2 where .. ) set ...
这是语法： update (select T1.* from T1, T2 where .. ) set ...
They require a join condition between T1 and T2, and a unique index on the join column in T2 to assure that the select returns at most one column per row in T1. Unfortunately for you there is another limitation that says, that only columns in one of the tables can be updated (T1 here).
它们需要 T1 和 T2 之间的连接条件，以及 T2 中连接列上的唯一索引，以确保选择最多返回 T1 中每行的一列。不幸的是，还有另一个限制是，只能更新其中一个表中的列（此处为 T1）。
So you need an intermediate table where you insert the changeset of project_ref to new-project_ref and then use this table to update T1 and T2 separately.
因此，您需要一个中间表，将 project_ref 的变更集插入到 new-project_ref 中，然后使用此表分别更新 T1 和 T2。
回答by APC
I'm afraid you're going to have to build a translation table. Something like:
恐怕您将不得不建立一个转换表。就像是：
project_test_data
-----------------
real_project_ref varchar2(30)
test_project_ref varchar2(30)
Then write update statements substituting the test version for the real version in the relevant tables.
然后在相关表中编写更新语句，将测试版本替换为真实版本。
Note that if you have defined foreign key relationships on these columns you will need to disable them, or at least make them deferrable and deferred. This is one reason to avoid using meaningful data as primary keys.
请注意，如果您在这些列上定义了外键关系，您将需要禁用它们，或者至少使它们可延迟和延迟。这是避免使用有意义的数据作为主键的原因之一。
回答by Mohammed Ismail
http://www.club-oracle.com/threads/updating-two-tables-in-single-query.6026/
http://www.club-oracle.com/threads/updating-two-tables-in-single-query.6026/
this link has detailed answer for your Question Refer it once :)
此链接对您的问题有详细的回答，请参考一次:)
