Re: UPDATE and SELECT in one statement

On Aug 2, 9:30 am, BMA <b..._at_nospam.abiware.be> wrote:
> Hello,
>
> I'm a bit brainwashed by the SQL Server Programming dialect...

:(

>
> I'm trying to update a column in a table, and immediatly do a select of
> that column in one statement.
>
> So, as I'm used to in SQL Server, i'd do :
>
> UPDATE myTable SET col=(select col+1 from otherTable) where recordnr=2
> SELECT col from myTable where recordnr=2
>
> This is a statement I send in one command to the database server, and
> is used to increment a sequence number for calculating unique invoice
> numbers. THis way, there cannot be any multi-user interference, so nobody
> generates the same invoice number.
>
> This works for SQL Server 2000/2005, but when testing on Oracle I get :
> ORA-00933 : SQL-command is not ended correct (roughly translated from
> dutch right now).
>
> I'm sending this statement from Omnis Studio on a windows XP SP 2 to an
> Oracle 9i Personnal Edition.
>
> Is it possible to send an update and a select in one statement?

No

It is possible to generate sequence numbers using the sequence object and (usually) triggers, or if you want a (bottlenecked under high load) solution like your sqlserver one that generates 'gap-free' sequences you can do that as well. sequences and triggers are well illustrated in the documentation.

I suspect that rather than batch 2 statements though you want to use the returning clause of the update statement

UPDATE myTable SET col=(select col+1 from otherTable) where recordnr=2 returning col into :bindvariable;

cheers Received on Thu Aug 02 2007 - 04:19:00 CDT