Using a CTE to update in Oracle

Manngo 0
Viewed 29

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
);
common-table-expression sql-update oracle
2 Answers

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
)
;
p3consulting0

The WITH should be inside the SET

UPDATE test SET data = (
    WITH vars(n) AS (
        select 5 FROM DUAL 
    )
    SELECT v.n * t.data
    FROM vars v
)
;
p3consulting0
Related