SQL & PL/SQL

Hi

I am trying to update a table based on an "ordered list" of values from another table. This is what I have:

--Script
drop table t1;

create table t1
( id number
, amt number
, dt date
, constraint t1_pk primary key( id )
);

drop table t2;

create table t2
( sessid number
, seqno number
, id number
, constraint t2_pk primary key( sessid, seqno, id )
)
organization index nologging compress;

--Insert some random rows in t1
insert into t1 values ( 1001, 28, sysdate );
insert into t1 values ( 1002, 12, sysdate+1 );
insert into t1 values ( 1003, 15.6, sysdate+5 );
insert into t1 values ( 1004, 8, sysdate+2.2 );

--Insert some random rows in t2 for sessid=100
insert into t2
select '100', rownum, id from t1 where amt <= 20
order by id;

--Insert some random rows in t2 for sessid=101
insert into t2
select '101', rownum, id from t1 where dt <= sysdate+4
order by id;

select * from t1;
1001 28 3/27/2007 3:10:45 PM
1002 12 3/28/2007 3:10:45 PM
1003 15.6 4/1/2007 3:10:45 PM
1004 8 3/29/2007 7:58:45 PM

--Note that sessid=100 used amt in the where clause, and sessid=101 used dt in the
--where clause. But both sessid are initially ordered by id
select * from t2;

100 1 1002
100 2 1003
100 3 1004
101 1 1001
101 2 1002
101 3 1004

--I would like to update t2 for sessid=100 such that seqno is now in dt order
--The following query shows the result.
select g.id
, h.seqno
, row_number() over( order by g.dt, g.id ) as new_seqno
from t2 h
inner join t1 g
on g.id = h.id
where h.sessid = 100;

1002 1 1
1004 3 2
1003 2 3

--After the update, t2 for sessid=101 should look like this
100 1 1002
100 3 1003
100 2 1004

--The following update does not work
update ( select h.sessid as session_id
, h.seqno as seqno
, row_number() over( order by g.dt, g.id ) as nseqno
from t2 h, t1 g
where g.id = h.id )
set seqno = nseqno
where session_id = 101
--ORA-01732: data manipulation operation not legal on this view