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 Received on Thu Aug 02 2007 - 03:30:15 CDT