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.
CodePudding user response:
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
)
;
CodePudding user response:
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
)
;