Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle alter table question

Re: oracle alter table question

From: Syltrem <syltremspammenot_at_videotron.com>
Date: Fri, 10 May 2002 16:02:14 -0400
Message-ID: <RFVC8.12046$a04.55024@tor-nn1.netcom.ca>


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...

>
> "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
>
>
Received on Fri May 10 2002 - 15:02:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US