Re: oracle alter table question

On Fri, 10 May 2002 16:02:14 -0400, "Syltrem" <syltremspammenot_at_videotron.com> wrote:

>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

Yes and No
It will limit the rows to be updated in the target table to the rows present in the source table.
Without the where clause ALL rows of the target table are going to be updated, and if there is no corresponding record the column will be nullified. So NO: this is a generic principle for updating a table with rows from a different table, and this is a specific case.

If you mean this sequence

> insert into dummy select pk, affected_column from source
>
> update source set affected column = NULL
>
> commit

with your second remark, YES that single commit after insert AND update was intentional. Of course the alter table will fire an implicit commit, but anyway.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 10 2002 - 17:49:26 CDT