Oracle-WITH CLAUSE =>合并? (语法错误，) [英] Oracle -- WITH CLAUSE => MERGE? (Syntax error, )

问题描述

我正在尝试让WITH子句在Oracle中与merge一起使用，但是由于某些原因，我无法使其工作.我敢肯定这很明显，但是我还没看过.

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.

-- 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.

推荐答案

除了在SELECT语句中，您不能在任何地方使用WITH子句. 请参阅此处的文档.:

You can't use the WITH clause anywhere but in a SELECT statement. See the documentation here.:

您可以在任何顶级SELECT语句中以及以下位置指定此子句 大多数类型的子查询.

You can specify this clause in any top-level SELECT statement and in most types of subqueries.

因此，您可以执行以下操作(已测试11g):

So, you can do something like this (11g tested):

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';

这篇关于Oracle-WITH CLAUSE =>合并? (语法错误，)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！