UPDATE and SELECT in one statement

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?

Thanks for any help (and sorry for my bad English ...)

Bram

On Aug 2, 10:30 am, BMA <b...@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?
>
> Thanks for any help (and sorry for my bad English ...)
>
> Bram

No, if you would have verified the UPDATE syntax in the documentation,
you would have noticed this is not possible.

update ...;
select ... ;

--
Sybrand Bakker
Senior Oracle DBA

On Aug 2, 9:30 am, BMA <b...@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

On Aug 2, 11:19 am, Niall Litchfield <niall.litchfi...@gmail.com>
wrote:
> On Aug 2, 9:30 am, BMA <b...@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

BMA 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?
>
> Thanks for any help (and sorry for my bad English ...)
>
> Bram

Completely unnecessary in Oracle ... it is easily done in a single
statement. There is a demo in Morgan's Library (www.psoug.org) under
UPDATE. Search for "RETURNING CLAUSE."

Here's a simple example that returns multiple columns:

conn hr/hr

var bnd1 NUMBER
var bnd2 VARCHAR2(30)
var bnd3 NUMBER

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000,
department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

print bnd1
print bnd2
print bnd3

bnd1, 2, and 3, in PL/SQL, are variables.

The RETURNING CLAUSE can also be used with INSERTs and DELETEs.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Thu, 02 Aug 2007 10:30:15 +0200, BMA wrote:


>
> Thanks for any help (and sorry for my bad English ...)
>

Thanks everybody for responding.
I wasn't aware of the return value of UPDATE-statements.

Now it works like a charm!

Thanks again,

Bram

Bram Mariën wrote:
> On Thu, 02 Aug 2007 10:30:15 +0200, BMA wrote:
>
>
>> Thanks for any help (and sorry for my bad English ...)
>>
>
> Thanks everybody for responding.
> I wasn't aware of the return value of UPDATE-statements.
>
> Now it works like a charm!
>
> Thanks again,
>
> Bram

Apparently you were not alone. <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org