SQL 使用 with 子句更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5380559/
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 statement using with clause
提问by gjvdkamp
I have a script that uses a stack of with clauses to come up with some result, and then I want to write that result in a table. I just can't get my head around it, could someone point me in the right direction?
我有一个脚本，它使用一堆 with 子句来得出一些结果，然后我想将该结果写入表中。我只是无法理解它，有人能指出我正确的方向吗？
Here's a simplified example that indicates what i want to do:
这是一个简单的例子，表明我想要做什么：
with comp as (
select *, 42 as ComputedValue from mytable where id = 1
)
update t
set SomeColumn = c.ComputedValue
from mytable t
inner join comp c on t.id = c.id
The real thing has quite a few with clauses that all reference each other, so any suggestions actually using the with clause would be highly preferred over refactoring it to nested subqueries.
真实的东西有很多 with 子句，它们都相互引用，因此任何实际使用 with 子句的建议都比将其重构为嵌套子查询更受欢迎。
Thanks in advance,
提前致谢，
Gert-Jan
格特-简
回答by gjvdkamp
If anyone comes here after me, this is the answer that worked for me.
如果有人在我之后来到这里，这就是对我有用的答案。
update mytable t
set z = (
with comp as (
select b.*, 42 as computed
from mytable t
where bs_id = 1
)
select c.computed
from comp c
where c.id = t.id
)
Good luck,
祝你好运，
GJ
国军
回答by Dave Costa
The WITH syntax appears to be valid in an inline view, e.g.
WITH 语法似乎在内联视图中有效，例如
UPDATE (WITH comp AS ...
SELECT SomeColumn, ComputedValue FROM t INNER JOIN comp ...)
SET SomeColumn=ComputedValue;
But in the quick tests I did this always failed with ORA-01732: data manipulation operation not legal on this view, although it succeeded if I rewrote to eliminate the WITH clause. So the refactoring may interfere with Oracle's ability to guarantee key-preservation.
但是在快速测试中，我这样做总是失败ORA-01732: data manipulation operation not legal on this view，尽管如果我重写以消除 WITH 子句，它还是成功了。因此，重构可能会干扰 Oracle 保证密钥保留的能力。
You should be able to use a MERGE, though. Using the simple example you've posted this doesn't even require a WITH clause:
不过，您应该能够使用 MERGE。使用您发布的简单示例，这甚至不需要 WITH 子句：
MERGE INTO mytable t
USING (select *, 42 as ComputedValue from mytable where id = 1) comp
ON (t.id = comp.id)
WHEN MATCHED THEN UPDATE SET SomeColumn=ComputedValue;
But I understand you have a more complex subquery you want to factor out. I think that you will be able to make the subquery in the USINGclause arbitrarily complex, incorporating multiple WITHclauses.
但我知道您有一个更复杂的子查询要分解。我认为您可以使USING子句中的子查询任意复杂，合并多个WITH子句。
回答by Yugesh
You can always do something like this:
你总是可以做这样的事情：
update mytable t
set SomeColumn = c.ComputedValue
from (select *, 42 as ComputedValue from mytable where id = 1) c
where t.id = c.id
You can now also use with statement inside update
您现在还可以在更新中使用 with 语句
update mytable t
set SomeColumn = c.ComputedValue
from (with abc as (select *, 43 as ComputedValue_new from mytable where id = 1
select *, 42 as ComputedValue, abc.ComputedValue_new from mytable n1
inner join abc on n1.id=abc.id) c
where t.id = c.id
