Syntax
NEXT VALUE FOR sequenceName
If this is the first use of the sequence generator, the generator returns its
START value. Otherwise, the INCREMENT value is added to the previous value
returned by the sequence generator. The data type of the value is the
dataType specified for the sequence generator.
If the sequence generator wraps around, then one of the following happens:
- If the sequence generator was created using the CYCLE keyword, the sequence
generator is reset to its START value.
- If the sequence generator was created with the default NO CYCLE behavior,
Derby throws an exception.
In order to retrieve the next value of a sequence generator, you or your
session's current role must have USAGE privilege on the generator.
A NEXT VALUE FOR expression may occur in the following places:
- SELECT statement:
As part of the expression defining a returned column in a SELECT list
- VALUES expression:
As part of the expression defining a column in a row constructor (VALUES
expression)
- UPDATE statement;
As part of the expression defining the new value to which a column is being
set
Only one NEXT VALUE FOR expression is allowed per sequence per statement.
The NEXT VALUE FOR expression is not allowed in any statement which has a
DISTINCT or ORDER BY expression.
The next value of a sequence generator is not affected by whether the user
commits or rolls back a transaction which invoked the sequence generator.
A NEXT VALUE expression may not appear in any of these situations:
- CASE expression
- WHERE clause
- ORDER BY clause
- Aggregate expression
- ROW_NUMBER function
- DISTINCT select list
sequenceName
[ schemaName. ] SQL92Identifier
If schemaName is not provided, the current schema is the default
schema. If a qualified sequence name is specified, the schema name cannot
begin with SYS.
Examples
VALUES (NEXT VALUE FOR order_id);
INSERT INTO re_order_table
SELECT NEXT VALUE FOR order_id, order_date, quantity
FROM orders
WHERE back_order = 1;
UPDATE orders
SET oid = NEXT VALUE FOR order_id
WHERE expired = 1;
