can you help me with this update query ?

Hi all

I have to do the following

the table schema is

ls_id
ls_parentval
ls_hierarchy
ls_orgindex

and this is the query

  select ls_id, rownum, orgindex
  from logistic_system
 where limit_status = 'Y'
 start with ls_id in
 (select ls_id
 from logistic_system
 where ls_parent = ' ')
 connect by ls_parent = prior ls_id

LS_ID                                                 ROWNUM  ORGINDEX
-------------------------------------------------- --------- ---------
LS00                                                       1
LS01                                                       2
LS05                                                       3
LS06                                                       4
LS07                                                       5
LS02                                                       6
LS03                                                       7
LS04                                                       8

now i want to update the orgindex with the same values of rownum it should be like this

LS_ID                                                 ROWNUM  ORGINDEX
-------------------------------------------------- --------- ---------
LS00
1                        1
LS01
2                        2
LS05
3                        3
LS06
4                        4
LS07
5                        5
LS02
6                        6
LS03
7                        7
LS04
8                        8

what kind of update query should i write ??? i tried this but it dint work

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

from logistic_system
where ls_hierarchy = 'STANDARD'
and a.ls_id = logistic_system .ls_id
start with ls_id in
(select ls_id

 from logistic_system
where ls_parent = ' ')
connect by ls_parent = prior ls_id);

but it shows me this result

SQL> select ls_id,limit_status, orgindex from logistic_system;

LS_ID                                              L  ORGINDEX
-------------------------------------------------- - ---------
LS00                                               Y         1
LS01                                               Y         1
LS02                                               Y         1
LS03                                               Y         1
LS04                                               Y         1
LS05                                               Y         1
LS06                                               Y         1
LS07                                               Y         1

so what should be update query ?? any idea or should i adopt some other method to do the updation (like record by record updation using procedure )

Regards & Thanks in Advance
~Parvinder

P.S : Also reply to parora_at_questone.com Received on Fri Dec 17 1999 - 09:30:43 CST