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

Originally posted by Stephan Grimm
> In order to emulate SEQUENCES with a table I would like to use
> something
> like the following nested statement to read and at the same time
> increase a number:
>
> UPDATE sequences SET seq_number = ((SELECT seq_number FROM sequences
> WHERE seq_id = $id) + 1) WHERE seq_id = $id;
>
> I guess the problem is that I try to use the result of a SELECT
> statement as operand for an arithmetic operation?!
> How can I get the number out of the subquery and increment it
> by 1 just
> like count() get's out a number of a result set ?
>
> BTW: I need a solution that works with Oracle as well as MySQL
> using the
> same syntax!
>
>
> Regards,
>
> Stephan Grimm

That would be:

UPDATE sequences SET seq_number = seq_number + 1 WHERE seq_id = :id;

But... you do realise what you are doing here? This table is going to be a big bottleneck for your system, as users will have to wait for a lock on sequences - only 1 transaction at a time can have a particular sequences record locked for update. You really don't want to go this way, you want to use Oracle sequences, and whatever MySQL's equivalent is. You can have a single procedure/function to encapsulate this DBMS-specific code.

--
Posted via http://dbforums.com