Oracle SQL - 如何从外部联接表进行更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19985101/
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 SQL - How do I update from an Outer Joined Table?
提问by DanK
The Problem
问题
I need to write an Update query where my SET references an outer joined table.
我需要编写一个更新查询，其中我的 SET 引用了一个外部连接表。
I can do this fairly easily with SQL Server but I'm having a heck of a time figuring out the syntax in Oracle as I'm only allow a single table in an update query.
我可以用 SQL Server 很容易地做到这一点，但我有很多时间弄清楚 Oracle 中的语法，因为我只允许在更新查询中使用单个表。
I have written the following Oracle query:
我编写了以下 Oracle 查询：
UPDATE SalesExt_tmp tmp
SET slsrep = (SELECT three_dig_rep
FROM dw_sls_rep_conv sls
WHERE sls.aims_rep = tmp.slsrep)
WHERE EXISTS (SELECT three_dig_rep
FROM dw_sls_rep_conv sls
WHERE sls.aims_rep = tmp.slsrep)
AND tmp.sysind = 'AIM';
This takes care of the intersection but I need to deal with values in SalesExt_tmp that do not have equivalent matches in dw_sls_rep_conv (I plan to add a case statement to set null values to a default value). To do this I need to set up dw_sls_rep_conv as an outer joined table. But this is where I get stuck.
这会处理交集，但我需要处理 SalesExt_tmp 中在 dw_sls_rep_conv 中没有等效匹配的值（我计划添加一个 case 语句以将空值设置为默认值）。为此，我需要将 dw_sls_rep_conv 设置为外部连接表。但这就是我陷入困境的地方。
SQL Server Example
SQL Server 示例
In SQL Server the solution is a piece of cake as you can have multiple tables in an Update Query:
在 SQL Server 中，解决方案是小菜一碟，因为您可以在更新查询中拥有多个表：
UPDATE SalesExt_tmp tmp
LEFT JOIN dw_sls_rep_conv sls ON sls.aims_rep = tmp.slsrep
SET tmp.slsrep = sls.three_dig_rep
WHERE tmp.sysind = 'AIM';
But I can't for the life of me figure out how to do this in Oracle. I understand that this query will allow my slsrep field to be set to NULL in some occasions which causes me to fear that this operation may not be allowed.
但是我一生都无法弄清楚如何在 Oracle 中执行此操作。我知道此查询将允许我的 slsrep 字段在某些情况下设置为 NULL，这让我担心可能不允许此操作。
Questions
问题
1) Firstly is this possible in Oracle? (It's got to be, right?)
1) 首先这在 Oracle 中是可能的吗？（一定是这样吧？）
2) How do I need to restructure my query to pull this off? I'm guessing my WHERE EXISTS clause needs to go... but I'm still stuck as to where to place my JOIN.
2）我需要如何重构我的查询以实现这一点？我猜我的 WHERE EXISTS 子句需要去......但我仍然不知道在哪里放置我的 JOIN。
回答by Przemyslaw Kruglej
If I understood you correctly, you want to set the value to NULL if there is no match in the dw_sls_rep_convtable? If so, just drop your EXISTScondition and all the rows will be updated:
如果我理解正确的话，如果dw_sls_rep_conv表中没有匹配项，您想将该值设置为 NULL吗？如果是这样，只需删除您的EXISTS条件，所有行都会更新：
UPDATE SalesExt_tmp tmp
SET slsrep = (SELECT three_dig_rep
FROM dw_sls_rep_conv sls
WHERE sls.aims_rep = tmp.slsrep)
WHERE tmp.sysind = 'AIM';
If there is a match in the dw_sls_rep_convtable, then the slsrepcolumn will be updated with selected value, otherwise, with NULL.
如果dw_sls_rep_conv表中有匹配项，则该slsrep列将使用选定的值进行更新，否则使用NULL.
回答by Patrick Bacon
Have you considered using a subquery in your where clause that performs the outer join as you stated like this:
您是否考虑过在 where 子句中使用子查询来执行外连接，如下所示：
UPDATE SalesExt_tmp tmp
SET slsrep = (SELECT three_dig_rep
FROM dw_sls_rep_conv sls WHERE sls.aims_rep = tmp.slsrep)
WHERE
tmp.rowid in
(SELECT tmp1.rowid
FROM SalesExt_tmp tmp1,
dw_sls_rep_conv sls
WHERE
tmp1.slsrep = sls.aims_rep (+)
AND tmp1.sysind = 'AIM' );
