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)
/
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With