Oracle: using CTE with update clause

Question

Can I update using a generic table expression in oracle database?

I get an error ORA-00928: missing SELECT keywordwhen I try to do 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

Answer 1

Since average salaryjust 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 this case, Oracle will first calculate the average (say 1234.4567), and then perform the upgrade.

Answer 2

Is it possible to do something like this in an oracle database?

, , . , . . ? CTE .

CTE, .. WITH , , . WITH, , , .

, WITH UPDATE.

,

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

MERGE WITH.

,

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>

Answer 3

Based on another answer to a correlated update of a stored key, here is another possible opportunity to use CTE with an update in Oracle SQL, avoiding duplication of the 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 the case of self-connecting, this can be simplified to the 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