| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: can you help me with this update query ?
A copy of this was sent to Parvinder Singh <parora_at_questone.com>
(if that email address didn't require changing)
On Fri, 17 Dec 1999 15:30:43 GMT, you wrote:
>
>Hi all
>
>I have to do the following
>
rownum is not a good thing to use like this.
rownum is assigned dynamically to rows as they exist the query processor -- before sorts/aggregates and the like.
In your case it is always one since the subquery is run ONCE per row in the update.
If you really truely want to do this:
tkyte_at_8i> create table emp as select * from scott.emp;
Table created.
tkyte_at_8i>
tkyte_at_8i>
tkyte_at_8i> select lpad('*',level*2,'*') || ename
2 from emp
LPAD('*',LEVEL*2,'*')||ENAME
**KING ****JONES ******SCOTT ********ADAMS ******FORD ********SMITH ****BLAKE ******ALLEN ******WARD ******MARTIN ******TURNER ******JAMES ****CLARK ******MILLER
14 rows selected.
Number col is the column we wish to update with rownum
tkyte_at_8i> alter table emp add number_col number 2 /
Table altered.
Temp will hold a rowid (PRIMARY KEY -- IMPORTANT) and the rnum
tkyte_at_8i> drop table temp;
Table dropped.
tkyte_at_8i>
tkyte_at_8i> create table temp ( rid primary key, rnum ) as
2 select rowid rid, rownum rnum
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 /
Table created.
We can now put the rnum back into the rows that are marked for update in TEMP.
tkyte_at_8i> update
2 ( select number_col, rnum
3 from emp , temp 4 where emp.rowid = temp.rid )5 set number_col = rnum
14 rows updated.
there you go:
tkyte_at_8i> select ename, number_col from emp
2 order by number_col
3 /
ENAME NUMBER_COL
---------- ----------
KING 1 JONES 2 SCOTT 3 ADAMS 4 FORD 5 SMITH 6 BLAKE 7 ALLEN 8 WARD 9 MARTIN 10 TURNER 11 JAMES 12 CLARK 13 MILLER 14
14 rows selected.
>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
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 17 1999 - 09:42:19 CST
 |
 |