Re: Dynamic SQL
Date: 14 Oct 2002 06:43:01 -0700
Message-ID: <aoehl5017q4_at_drn.newsguy.com>
In article <pGpq9.1090$Lb1.143860_at_news20.bellglobal.com>, "M says...
>
>This code will do the update for a single column (fix it to do whatever
>column or columns you need);
>
>UPDATE TAB1 X SET col1=( SELECT col1
> FROM TAB1
> WHERE ROWID=( SELECT ROWID
> FROM TAB1
> WHERE col1 IS NOT NULL
> AND ROWID>x.ROWID
> AND rownum=1
> ) )
>WHERE col1 IS NULL;
why would it? There is no such thing as "order" in a table. How do you know the "first" rowid you hit will be the rowid it'll give you back?????
Consider:
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
C
-
A
D
E
F
B
9 rows selected.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> UPDATE Tab1 X SET col1=( SELECT col1
2 FROM TAB1 3 WHERE ROWID=( SELECT ROWID 4 FROM TAB1 5 WHERE col1 IS NOT NULL 6 AND ROWID>x.ROWID 7 AND rownum=1 8 ) )
9 WHERE col1 IS NULL;
4 rows updated.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
C
-
A
D
D
E
E
F
F
B
B
9 rows selected.
ops$tkyte_at_ORA920.US.ORACLE.COM> rollback;
Rollback complete.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
C
-
A
D
E
F
B
9 rows selected.
ops$tkyte_at_ORA920.US.ORACLE.COM> create index tab1_idx on tab1(col1);
Index created.
ops$tkyte_at_ORA920.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'TAB1', numrows=>1000000, numblks => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> UPDATE Tab1 X SET col1=( SELECT col1
2 FROM TAB1 3 WHERE ROWID=( SELECT ROWID 4 FROM TAB1 5 WHERE col1 IS NOT NULL 6 AND ROWID>x.ROWID 7 AND rownum=1 8 ) )
9 WHERE col1 IS NULL;
4 rows updated.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
C
-
A
B
D
B
E
B
F
B
B
9 rows selected.
Query plans, numbers of rows, whether it is currently raining or sunny -- all of those will affect this.
NEVER write queries that rely on things "being in some sort of implicit order". Rowid isn't even very "orderable" -- Here is another example:
ops$tkyte_at_ORA920.US.ORACLE.COM> column col1 format a8 ops$tkyte_at_ORA920.US.ORACLE.COM> column col2 noprint ops$tkyte_at_ORA920.US.ORACLE.COM> column col3 noprint ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> create table tab12 ( col1 char(1),
3 col2 varchar2(4000) default rpad('*',4000,'*'), 4 col3 varchar2(3000) default rpad('*',3000,'*') 5 );
Table created.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> insert into tab1 (col1,col2,col3) values (
'A',null,null );
1 row created.
ops$tkyte_at_ORA920.US.ORACLE.COM> insert into tab1 (col1) values ( 'B' );
1 row created.
ops$tkyte_at_ORA920.US.ORACLE.COM> insert into tab1 (col1) values ( NULL );
1 row created.
ops$tkyte_at_ORA920.US.ORACLE.COM> insert into tab1 (col1,col2,col3) values ( 'C',null,null );
1 row created.
ops$tkyte_at_ORA920.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte_at_ORA920.US.ORACLE.COM> update tab1 2 set col2 = rpad('*',4000,'*'), col3 = rpad('*',3000,'*') 3 where col1 = 'C';
1 row updated.
ops$tkyte_at_ORA920.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte_at_ORA920.US.ORACLE.COM> update tab1 2 set col2 = rpad('*',400,'*'), col3 = rpad('*',300,'*') 3 where col1 = 'A';
1 row updated.
ops$tkyte_at_ORA920.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> set null "(NULL)" ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
COL1
A
B
(NULL)
C
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1 order by rowid;
COL1
A
B
C
(NULL) ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> UPDATE Tab1 X SET col1=( SELECT col1
2 FROM TAB1 3 WHERE ROWID=( SELECT ROWID 4 FROM TAB1 5 WHERE col1 IS NOT NULL 6 AND ROWID>x.ROWID 7 AND rownum=1 8 ) )
9 WHERE col1 IS NULL;
1 row updated.
ops$tkyte_at_ORA920.US.ORACLE.COM>
ops$tkyte_at_ORA920.US.ORACLE.COM> select * from tab1;
COL1
A
B
(NULL)
C
So, why isn't (NULL) set to C? cause C doesn't come after it "by rowid" -- a full scan sees C after (NULL) but the rowid for is apparently LESS THEN the rowid for the NULL row...
without an order by -- there is not any concept of "order" in the world.
>
>"arnaud herve" <dillonais_at_wanadoo.fr> wrote in message
>news:aobc2h$lso$1_at_news-reader10.wanadoo.fr...
>> I've a table T like that
>> X Y
>> -------
>> A NULL
>> NULL C
>> D E
>> F NULL
>>
>> My Problem.
>> I want to replace values equal to NULL by the value which follow them
>> In my case, I'll get.
>> A C
>> D C
>> D E
>> F NULL
>> I used a curosr but how to make a dynamic update?
>>
>> according to me,It would be like
>> _____________________________________
>> .........
>> for i in 1..sql%rowcount-1 loop
>> if X is null
>> update T set X = select X from T
>> where rownum = i+1;
>> end loop;
>> ..........
>> _________________________________________
>>
>> it's a sample of code, I know a dirty code, but simply to show you, what
I
>> want to get.
>>
>>
>>
>
>
-- Thomas Kyte (tkyte_at_oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Oct 14 2002 - 15:43:01 CEST