Re: sql*plus question

Thanks very much - it works a treat!

>==========Joost Ouwerkerk, 17/07/97==========
>
>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)
>
>

Sally Pearce
Financials DBA(for my sins) Received on Fri Jul 18 1997 - 00:00:00 CEST