Re: UPDATE and SELECT in one statement

On Aug 2, 11:19 am, Niall Litchfield <niall.litchfi..._at_gmail.com> wrote:
> 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

It seems to me that solution proposed by Niall is very appropriate and clear,
but SQL Server sintax is somewhat weid ;)

Bye
 Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Thu Aug 02 2007 - 05:23:36 CDT