Re: sql*plus question

[Quoted] Try this -- Update the detail BEFORE the master or you won't be able to lookup the ref column (since it exists only in the master and the invoice number -which you're UPDATING- is the foreign key).

UPDATE detail SET invoice=97000000+invoice  WHERE EXISTS (SELECT 0 FROM master

                   WHERE ref='CHANGE' AND
                   detail.invoice=master.invoice);

UPDATE master SET invoice=97000000+invoice   WHERE ref='CHANGE';

Joost Ouwerkerk
HSC Foundation
Toronto, Canada

On Thu, 17 Jul 1997 12:55:21 GMT, smp <> wrote:

>Hi,
> This has got to be an easy one but my brain has gone dead and I can't work it
>out.
> I would appreciate any clues to the answer.
>
>I have two tables header and detail which have a master-detail relationship
>with invoice as the key.
>
>header
> invoice varchar2(10)
> ref varchar2(5)
> other stuff. .
>
>detail
> invoice varchar2(10)
> other stuff. . .
>
>I want to amend the invoice number for both header and detail if the ref in
>the header holds a particular value
>
>eg should become
>Header:-
> invoice 123456 invoice 97123456
> bref CHANGE bref CHANGE
>
>Detail:-
> invoice 123456 invoice 97123456
>
>I would like to do this in one pass if possible
>
>Help!!!!
>Yours gratefully
>
>
>Sally Pearce
>Financials DBA(for my sins)
Received on Thu Jul 17 1997 - 00:00:00 CEST