Re: can you help me with this update query

Try removing "(select" from row 3 and ")" from the last row.

Parvinder Singh wrote:

> 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 - 14:27:49 CST