oracle 甲骨文——带子句 => 合并？（语法错误，）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6673806/
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 -- WITH CLAUSE => MERGE? (Syntax error, )
提问by cwallenpoole
I'm trying to get the WITH clause to work with merge in Oracle, but for some reason I can't get it working. I'm sure it is something obvious, but I just haven't seen it.
我试图让 WITH 子句与 Oracle 中的合并一起使用，但由于某种原因我无法让它工作。我确定这是显而易见的事情，但我只是没有看到。
-- behold, the wonders of fake data
WITH X AS (
SELECT
'moo' AS COW,
'woof' AS CAT,
(SELECT MAX( DECIBELS ) FROM ANIMALIA WHERE COW = 'moo' ) AS DECIBELS
FROM DUAL )
MERGE INTO ANIMALIA D
USING X
WHEN MATCHED THEN
UPDATE SET D.COW = X.COW;
EDIT
编辑
I actually found out how to manage this (before I submitted the question), but I think that since it took me quite some time to find the answer, hopefully leaving this question up will mean that the next person will find it in not quite so much time.
我实际上已经找到了如何解决这个问题（在我提交问题之前），但我认为因为我花了很长时间才找到答案，希望留下这个问题意味着下一个人会发现它不是那么简单很多时间。
I will post the answer in a day or so, but if someone else posts it in the meanwhile they'll get the points.
我会在一天左右发布答案，但如果其他人在此期间发布答案，他们将获得积分。
回答by DCookie
You can't use the WITH clause anywhere but in a SELECT statement. See the documentation here.:
除了在 SELECT 语句中，您不能在任何地方使用 WITH 子句。 请参阅此处的文档。：
You can specify this clause in any top-level SELECT statement and in most types of subqueries.
您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。
So, you can do something like this (11g tested):
因此，您可以执行以下操作（经过 11g 测试）：
MERGE INTO animalia d
USING (WITH X AS
(SELECT 'moo' AS COW, 'woof' AS CAT,
(SELECT MAX( DECIBELS )
FROM ANIMALIA
WHERE COW = 'moo' ) AS DECIBELS
FROM DUAL )
SELECT * FROM X) q ON (1 = 1)
WHEN MATCHED THEN UPDATE SET d.cow = q.cow||' and more';
