Update Command Related !

Hi All

I have got stuck in a very stupid kindda problem (i guess thats stupid)

the table schema is

pg_id
pg_parentval
pg_hierarchy
pg_orgindex

The orgindex field has to be updated with the following query

(select rownum

from program
where pg_hierarchy = 'STANDARD'
start with pg_val in
(select pg_val

 from program
where pg_parent = ' ')
connect by pg_parent = prior pg_val)

what i am trying to do is something like this

update program a
set limit_status = 'Y', orgindex = rownum,
(select rownum

from program
where pg_hierarchy = 'STANDARD'
and a.pg_val = program.pg_val
start with pg_val in
(select pg_val

 from program
where pg_parent = ' ')
connect by pg_parent = prior pg_val);

but it doesn't work ...Any idea what kind of query would it be

or the other option which i can think is take the result of the inner select (along with the primary key of the table) into a view and then update my orgindex as per the view
what do you say ?

Thanks in Advance

Regards
~Parvinder Received on Thu Dec 16 1999 - 08:00:24 CST