Re: UPDATE and SELECT in one statement
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Thu, 02 Aug 2007 05:12:47 -0700
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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- References:
- UPDATE and SELECT in one statement
- From: BMA
- UPDATE and SELECT in one statement
- Prev by Date: Re: trying to create text file using utl_file
- Next by Date: Re: UPDATE and SELECT in one statement
- Previous by thread: Re: UPDATE and SELECT in one statement
- Next by thread: Re: UPDATE and SELECT in one statement
- Index(es):
Relevant Pages
|
