5

I am trying a simple merge statement using a CTE(Common table expression) . But it gives an error 

MERGE INTO  emp targ USING (
*
ERROR at line 4:
ORA-00928: missing SELECT keyword

Is the CTE not allowed in a merge statement? My Sql is below:

WITH cte AS (
  SELECT empno, ename 
    FROM EMP)
MERGE INTO emp targ USING (SELECT * 
                             FROM cte) src
  ON (targ.empno = src.empno)
WHEN MATCHED THEN update 
   SET targ.ename = src.ename
WHEN NOT MATCHED THEN insert
    (empno,ename)
  VALUES
    (src.empno,src.ename)
/
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
josephj1989
  • 9,509
  • 9
  • 48
  • 70

1 Answers1

5

The WITH clause is for use with a SELECT statement.

From the documents: "You can specify this clause in any top-level SELECT statement and in most types of subqueries." (emphasis mine).

Here is a possible workaround if you really need to do this, from ORAFAQ. The main blog writeup is here.

DCookie
  • 42,630
  • 11
  • 83
  • 92