Re: Update question

On Fri, 23 May 2003 11:46:07 -0400, "Phil Kaufman" <philk_at_dbcsmartsoftware.com> wrote:

>Hi everyone,
>
>This is for Oracle 8.0.5 (and above possibly).
>
>When I attempt to write an update statement with the following syntax:
>
>UPDATE <some table>
> SET <some column> = (SELECT 1 FROM DUAL) +
> (SELECT 2 FROM DUAL)
>/
>
>I get an error:
>
> SET number_of_batches = (SELECT 1 FROM DUAL) +
> *
>ERROR at line 2:
>ORA-00933: SQL command not properly ended
>
>My question: How to have a single update contain two sub-select? I tried
>the idea of:
>
>UPDATE <some table>
> SET <some column> = (SELECT tab1.col1 + tab2.col1
> FROM (SELECT 1 AS col1 FROM DUAL tab1),
> (SELECT 2 AS col1 FROM DUAL tab2)
>/
>
>but, no good, same error.
>
>Anyone have any ideas?
>
>Thanks very much in advance.
>
>Phil
>

Please try to differentiate between subqueries and inline-views. The first update is using inline views, the proper syntax for a subquery would just have been
= (select 1 + 2 from dual)

And NO: 8.0.5 definitely doesn't support inline views in an update. Time to a) upgrade or b) learn the SQL syntax

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 23 2003 - 12:21:39 CDT