Search code examples
oracle-databasesql-updatecommon-table-expression

Using a CTE to update in Oracle


For logistic reasons, I want to set a value in a CTE to use in an UPDATE statement. Here is a simplified version:

with vars as (select 5 as n from dual)
update test set data=data*vars.n, more=more*vars.n;

Of course, in real life, the CTE will calculate a value from another source, but this should make the point.

I have sample at https://dbfiddle.uk/tUJoX5uw .

I get the error

ORA-00928: missing SELECT keyword

Is it possible to use a CTE this way for an UPDATE statement?

I know about some other SO questions, but generally they either end up using MERGE, or say that you can’t update the CTE itself. I don’t want to update the CTE, just to use the results. This is a question about the UPDATE statement.

Update

I have accepted an answer below. Here is a simplified version of the solution:

UPDATE TEST t
SET (t.data, t.more) = (
    SELECT vars.n*t.data, vars.n*t.more FROM (
        SELECT 5 AS n FROM dual
    ) vars
);

Solution

  • This will not work:

    UPDATE TEST t SET (t.data, t.more) = (
        WITH VARS(n) AS (
            select 5 FROM DUAL 
        )
        SELECT v.n * t.data, v.n * t.more
        FROM vars v
    )
    ;
    
    SQL Error: ORA-01767: UPDATE ... SET expression must be a subquery
    

    But this works:

    UPDATE TEST t SET (t.data, t.more) = (
        SELECT (select 5 from dual) * t.data, (select 5 from dual) * t.more
        FROM DUAL
    )
    ;