Re: SQL question (using result of nested statement in arithmetic operation)

On 25 Apr 2003 11:16:18 -0700, 71062.1056_at_compuserve.com (--CELKO--) wrote:

>UPDATE Sequences
> SET seq_nbr = (SELECT MAX(seq_nbr)
> FROM sequences) + 1
> WHERE seq_id = :id;
>
>Without any DDL, I can only guess. It looks like you need a scalar
>subquery expression, but I have no idea what seq_nbr and seq_id mean
>since we have no DDL or specs for the table.

This solution is
a) syntactically incorrect
b) can not be used in a multi-user environment, as two different sessions may interfere with each other

You'll need to
select for update the record first (this will also lock the record), *without adding ANYTHING!!!*
update the table

Evidently this solution is still non-scalable, and shouldn't be used in an Oracle environment. the Op MUST encapsulate everything in a procedure and use proper sequences in Oracle.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Apr 25 2003 - 15:14:39 CDT