Oracle: Using CTE with update clause

Question

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

Solution 1

Since average salary just a scalar value you can do

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

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

Solution 2

Can I do something like this in oracle database?

Well, it is not about whether you could do it or not. It is about whether you need to do it or not. In your query I don't see any filter criteria. You want to update all the rows? I don't see a need of CTE in your case.

When do you need a CTE, i.e. a with clause as a sub-query factoring method whenever you have a scenario where the sub-query is executed multiple times. You use a WITH clause to make sure the subquery is executed once, and the resultset is stored as a temp table.

Yes, you could use WITH clause for an UPDATE statement.

For example,

UPDATE TABLE t
SET t.column1, t.column2 = (SELECT column1, column2 FROM 
                                       (
                                        WITH cte AS(
                                   SELECT ... FROM another_table
                                                 )
                                         SELECT * FROM cte
                                        )

You could use a MERGE statement USING the WITH clause.

For example,

SQL> MERGE INTO emp e USING
  2  (WITH average AS
  3    (SELECT deptno, AVG(sal) avg_sal FROM emp group by deptno)
  4  SELECT * FROM average
  5  ) u
  6  ON (e.deptno = u.deptno)
  7  WHEN MATCHED THEN
  8  UPDATE SET e.sal      =
  9    CASE
 10      WHEN e.sal <= u.avg_sal
 11      THEN e.sal * 1.05
 12      ELSE e.sal * 1.03
 13    END
 14  /

14 rows merged.

SQL>

Solution 3

Basing on another answer on correlated update of key-preserved view, here is another possible option to use CTE with update in Oracle SQL avoiding duplication of where clause:

update (
    with cte as (select avg(salary) average_salary from instructor)
    select id, salary, cte.average_salary from instructor cross join cte
    where <some_condition>
)
set salary = case
    when salary <= average_salary/2 then salary * 1.1 
    when salary <= average_salary then salary * 1.05 
    else salary * 1.03                                     
end

In case of self-join this can be simplified to CTE-less version:

update (
    select id, salary, (select avg(salary) from instructor) average_salary 
    from instructor
    where <some_condition>
)
set salary = case
    when salary <= average_salary/2 then salary * 1.1 
    when salary <= average_salary then salary * 1.05 
    else salary * 1.03                                     
end