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

"Stephan Grimm" <stephan.grimm_at_igd.fhg.de> schrieb im Newsbeitrag news:3EA94CB4.DAC5C024_at_igd.fhg.de...
> 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;

This works with MS SQL Server, maybe it does the job with MySQL, too:

UPDATE sequences SET seq_number = (SELECT seq_number + 1 FROM sequences WHERE seq_id = $id) 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!

If you are working with MySQL 5.x then you could create a stored procedure that encapsulates the different syntaxes.

    robert Received on Fri Apr 25 2003 - 10:57:43 CDT