shashankgaurav shashankgaurav - 2 months ago 22
SQL Question

Oracle: Using CTE with update clause

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

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 salary * 1.05
else salary * 1.03
end

Answer

Since average salary just a scalar value you can do

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

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

Comments