Oracle -- WITH CLAUSE => MERGE? (Syntax error, )
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 dUSING (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';