Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle alter table question
Sybrand,
Very good explanation of the process, but there's one thing that's not clear to me:
What will this select do ?
where exists
(select 'x'
from dummy d
where d.primary key = source.primary key
I figure it's to avoid setting to nul, rows that were added to the table
source after dummy was built. Is that right?
Also if this is the reason, then it's important we do the 1st commit after
both insert AND update have been performed in order to affect the same rows
in the 2 instructions.
Is my understanding ok?
Thanks
-- Syltrem http://pages.infinit.net/syltrem (OpenVMS related web site - en français) To reply to myself directly, remove .spammenot from my address "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message de news: udl5onf1se8h8d_at_corp.supernews.com...Received on Fri May 10 2002 - 15:02:14 CDT
>
> "Rayden" <durlecht_at_remove-this.msu.edu> wrote in message
> news:abe2na$2gfn$1_at_msunews.cl.msu.edu...
> > hey,
> >
> > i have a table with which i need to change one of the columns, however
> > because i have data in the tables it will not let me do this (oracle
> 8i).the
> > column change is to increase a varchar in size. how in the world do i do
> > this? i tried to exp the data, however it does the table as well. i am a
> > little lost here, so any help would be appreciated.
> >
> >
> > tom
> >
> >
>
> Here's how in pseudo code
> create a dummy table with the primary key of the source table and the
> affected column
>
> insert into dummy select pk, affected_column from source
>
> update source set affected column = NULL
>
> commit
>
> alter table source
> rem restore the original data
> update table source
> set affected_column =
> (select affcted_column
> from dummy d
> where d.primary key = source.primary key
> )
> where exists
> (select 'x'
> from dummy d
> where d.primary key = source.primary key
> )
> commit;
> drop table dummy;
>
>
> Hth
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>