sql - ORA-00928 : missing SELECT keyword: update using with

Question

我收到 ORA-00928:在将“update”与“with”结合使用时出现缺少 SELECT 关键字错误。

这是报错。

with wr_double as
(select...)

update work_request r 
set r.name = r.name || '_old'
where exists 
(select 1 from wr_double wd
where wd.name = r.name and wd.wr_id = r.id)

但这很好用

with wr_double as
(select...)

select * from work_request r 
where exists 
(select 1 from wr_double wd
where wd.name = r.name and wd.wr_id = r.id)

此外，如果我将来自 with 的子查询放在更新正文中，它也可以正常工作。

update work_request r 
set r.name = r.name || '_old'
where exists 
(select 1 from 
(select 
wr.name, 
wr.id as wr_id, 
dup_wr.count,
d.id as d_id, 
d.create_date
from 
(select...) wd
where wd.name = r.name and wd.wr_id = r.id)

我不能以这种方式将“with”与“更新”一起使用吗？

Answer 1

你必须按照下面的方式写，因为 CTE 是 SELECT 的一部分而不是 UPDATE

update work_request 
set name = name || '_old'

   where exists (
      with wr_double as
         (select...)
      select 1 from wr_double wd  wd.name = work_request.name and wd.wr_id = work_request.id
     );