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

I do not have much experience with Oracle database, but I think you're getting an error because you want to use a CTE (that returns results in the form of a table) inside an update. Why don't you just store the result of `SELECT avg(salary) FROM instructor` into a variable and use it in the UPDATE? I think it's the simplest way to do what you want.. — Alberto Solano, Mar 04 '15 at 07:51
Okay I will do that. But i have a question can we use with and update clause together? — shashankgaurav, Mar 04 '15 at 07:53
As I said, a [CTE](http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression) returns results in the form of a table, and then you can use it through JOINs with other tables, or use in other ways (but not in the way you're using it) in order to have the results you want. So, to answer your question: No, you cannot use a CTE (it's not named 'with') in that way like yours. — Alberto Solano, Mar 04 '15 at 08:01
It is syntactically incorrect. Yes, you can use WITH clause in an update statement, See my answer. — Lalit Kumar B, Mar 04 '15 at 08:24

Lalit Kumar B · Answer 1 · 2015-03-04T08:23:59.660

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>

score 3 · Accepted Answer · edited Sep 16 '16 at 14:58

3

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.

edited Sep 16 '16 at 14:58

Vadzim

24,954
11
143
151

answered Mar 04 '15 at 07:43

Dmitry Bychenko

180,369
20
160
215

1

But the above query is valid right? So we can use 'with' clause with 'update' right? – shashankgaurav Mar 04 '15 at 07:47
@shashankgaurav In this case you don't need a CTE to compute the average since it's calculated inside CASE WHEN statement. – Alberto Solano Mar 04 '15 at 07:56
1

@alberto I was my talking about my query. – shashankgaurav Mar 04 '15 at 07:59
@shashankgaurav Indeed, you cannot use CTE and UPDATE all together like in your query. That's why Dmitry slightly modified your query. – Alberto Solano Mar 04 '15 at 08:03

score 2 · Answer 3 · edited May 23 '17 at 12:34

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

Here is more on "key preserved" thing: http://dba.stackexchange.com/questions/38728/oracle-non-key-preserved-table-should-be — Vadzim, Oct 13 '16 at 20:38

Oracle: Using CTE with update clause

3 Answers3

Linked