Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I guess you mean update test set col1 = 1 + (select 1 from DUAL ); –  bernd_k Mar 31 '11 at 13:17
    
put it into your answer and I can accept it –  bernd_k Mar 31 '11 at 14:40
    
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. –  Leigh Riffel Mar 31 '11 at 17:36
    
OK. I'll wait some time. –  bernd_k Apr 1 '11 at 6:11
add comment

3 Answers

I found the following work around:

update test set col1 = NVL((select 1  from DUAL ), NULL) + 1;
share|improve this answer
add comment

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.

share|improve this answer
add comment

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;
share|improve this answer
    
I see the same behavior difference using bernd_k's code block. –  Leigh Riffel Mar 30 '11 at 16:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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