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

Question

I am trying to get the WITH clause to work with federation in Oracle, but for some reason I cannot get it to work. I'm sure this is something obvious, but I just did not see 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 really learned how to manage this (before I submitted the question), but I think that since it took me a while to find the answer, we hope that leaving this question will mean that the next person will find it in so much time.

I will send the answer in a day or so, but if someone else posts it, meanwhile they will get points.

Answer 1

You cannot 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 checked):

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