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.

  • I guess you mean update test set col1 = 1 + (select 1 from DUAL );
    – bernd_k
    Mar 31, 2011 at 13:17
  • I added my workaround to the answer I gave, but I'm not sure you should accept it quite yet. I'd hold out a bit longer to see if someone can post an Oracle bug on it or at least a better explanation of the behavior. Mar 31, 2011 at 17:36
  • OK. I'll wait some time.
    – bernd_k
    Apr 1, 2011 at 6:11

3 Answers 3


I found the following work around:

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

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.


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;
  • I see the same behavior difference using bernd_k's code block. Mar 30, 2011 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.