Oracle CTE 合并
我正在尝试使用 CTE（公用表表达式）的简单合并语句。但它给出了一个错误
MERGE INTO emp targ USING (
*
ERROR at line 4:
ORA-00928: missing SELECT keyword
Is the CTE not allowed in a merge statements?我的Sql如下：
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)
/
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)
/
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
WITH 子句与 SELECT 语句一起使用。
来自文档：
“您可以在任何顶级 SELECT 语句和大多数类型的子查询中指定此子句。” （强调我的）。
如果您确实需要这样做，这里有一个可能的解决方法，来自 ORAFAQ。 主要博客文章在这里。
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.