Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can't we use CURRVAL or/and NEXTVAL in a where clause of an UPDATE statement?

3264593Jun 23 2016 — edited Jun 24 2016

Hello guys,

In Managing Sequences it says:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm

Uses and Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in the following places:

  • VALUES clause of INSERT statements
  • The SELECT list of a SELECT statement
  • The SET clause of an UPDATE statement

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:

  • A subquery in a DELETE, SELECT, or UPDATE statement
  • A query of a view or of a materialized view
  • A SELECT statement with the DISTINCT operator
  • A SELECT statement with a GROUPBY clause or ORDERBY clause
  • A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
  • The WHERE clause of a SELECT statement
  • The DEFAULT value of a column in a CREATETABLE or ALTERTABLE statement
  • The condition of a CHECK constraint

____________________________________________________________________________________________________

--i was trying to do something like this:

CREATE SEQUENCE random_seq

START WITH 500

INCREMENT BY 1

CACHE 10;


CREATE SEQUENCE round_seq

INCREMENT BY 1

NOCACHE;

INSERT INTO departments(department_id,department_name,manager_id)

VALUES(round_seq.NEXTVAL, 'Department Round_seq', 100);

INSERT INTO employees(employee_id, last_name, email, hire_date)

VALUES(random_seq.NEXTVAL,'Lalo', 'lalo@', SYSDATE);

--until here, everything was fine.

UPDATE employees

SET department_id = round_seq.CURRVAL

WHERE employee_id = random_seq.CURRVAL;

/* and here i got:

SQL Error: ORA-02287: sequence number not allowed here

02287. 00000 -  "sequence number not allowed here"

*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate

           here in the statement.

*Action:   Remove the sequence number.*/

--then i tried


UPDATE employees

SET department_id = round_seq.CURRVAL

WHERE employee_id = 500;

and it was good.

_____________________________________________________________________________

So i'm guessing we cant use a pseudocolumns(CURRVAL or NEXTVAL) anywhere in a WHERE clause. Is that right?

This post has been answered by Sven W. on Jun 24 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2016
Added on Jun 23 2016
8 comments
3,774 views