update hs_FAVORRANK set NUMRANKGUID=2223 where NUMFRGUID=HSSEQ_FAVORRANK.NEXTVALORA-02287: 此处不允许序号
You can use CURRVAL and NEXTVAL in: 1. The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view 2. The SELECT list of a subquery in an INSERT statement 3. The VALUES clause of an INSERT statement 4. The SET clause of an UPDATE statement ------------------------------------------- You cannot use CURRVAL and NEXTVAL: 1. A subquery in a DELETE, SELECT, or UPDATE statement 2. A query of a view or of a materialized view 3. A SELECT statement with the DISTINCT operator Pseudocolumns 4.A SELECT statement with a GROUP BY clause or ORDER BY clause 5. A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator 6.The WHERE clause of a SELECT statement 7. DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement 8.The condition of a CHECK constraint
不能寫在where語句裡面,你的寫法不合法。
CREATE SEQUENCE seq_dft_dyjzls_id INCREMENT BY 1 START WITH 1; 2.获取序号值 获取当前序号:SELECT seq_dft_dyjzls_id.CURRVAL FROM DUAL; 获取下一个序号:SELECT seq_dft_dyjzls_id.NEXTVAL FROM DUAL;
create trigger TABLE1_tri before insert on TABLE1 for each row begin select SEQ.nextval into :new.NUMFRGUID from dual; end; / update TABLE1 set GUID=(select max(NUID) from TABLE2)
1. The SELECT list of a SELECT statement that is not contained in a subquery,
materialized view, or view
2. The SELECT list of a subquery in an INSERT statement
3. The VALUES clause of an INSERT statement
4. The SET clause of an UPDATE statement
-------------------------------------------
You cannot use CURRVAL and NEXTVAL:
1. A subquery in a DELETE, SELECT, or UPDATE statement
2. A query of a view or of a materialized view
3. A SELECT statement with the DISTINCT operator Pseudocolumns
4.A SELECT statement with a GROUP BY clause or ORDER BY clause
5. A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
6.The WHERE clause of a SELECT statement
7. DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
8.The condition of a CHECK constraint
2.获取序号值
获取当前序号:SELECT seq_dft_dyjzls_id.CURRVAL FROM DUAL;
获取下一个序号:SELECT seq_dft_dyjzls_id.NEXTVAL FROM DUAL;
before insert on TABLE1
for each row
begin
select SEQ.nextval into :new.NUMFRGUID from dual;
end;
/
update TABLE1 set GUID=(select max(NUID) from TABLE2)