Oracle — WITH CLAUSE => MERGE? (Syntax error, )

Question

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.

Answer 1

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

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

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