Oracle:将CTE与update子句一起使用 [英] Oracle: Using CTE with update clause

问题描述

我可以使用oracle数据库中的公用表表达式进行更新吗?

Can I make an update using common table expression in oracle database?

尝试此操作时出现错误ORA-00928: missing SELECT keyword:

I am getting error ORA-00928: missing SELECT keyword when I am trying this:

with average as (SELECT avg(salary) FROM instructor)
update instructor
               set salary = case
                   when salary <= average then salary * 1.05 
                   else salary * 1.03                                     
               end

推荐答案

由于average salary只是标量值，您可以做到

update instructor
   set salary = case
       when salary <= (select avg(t.salary) from instructor t) then salary * 1.05 
       else salary * 1.03                                     
   end

在那种情况下，Oracle首先计算平均值(例如1234.4567)，然后执行 update .

In that case Oracle first compute the average (say 1234.4567) and then perform the update.

这篇关于Oracle:将CTE与update子句一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！