How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

Question

I have a problem with an update statement, that I developed under 11g, when running under 10g.

I reduced the problem to:

Create table test (
   col1 number(10)
);

insert into test values (1);

update test set col1 = (select 1  from DUAL ) + 1;

select * from test;

When executing on Oracle 10, I get the following error:

update test set col1 = (select 1  from DUAL ) + 1
Error at line 1
ORA-00933: SQL-Befehl wurde nicht korrekt beendet

Any ideas, how I can modify the statement to run under 10g? I want to use a single statement.

Answer 1

I found the following work around:

update test set col1 = NVL((select 1  from DUAL ), NULL) + 1;

Answer 2

The documentation for the Update statement allows this syntax even in 10g, so perhaps this is a bug. I looked for one on My Oracle Support, but couldn't find one. Perhaps you should open a SR with Oracle to see what they say.

As a workaround if you re-arrange the statement to have the literal first it will work on both versions.

update test set col1 = 1 + (select 1 from DUAL );

Another workaround is to wrap the function in to_number. I don't see why either should be necessary. Updating a column to the results of a compound expression that contains a scalar subquery expression added to a value is supported by the syntax and should work.

Similar behavior found here also with no resolution.

Answer 3

I think you may just be missing a semi-colon after the update statement. Are you running the exact same code on 11g as 10g?

update test set col1 = (select 1  from DUAL ) + 1;