Re: sql*plus question
Date: 1997/08/03
Message-ID: <33E4A724.230E_at_sprynet.com>#1/1
> 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
> >
Assuming you have no integrity constraints,
You may write a SQL similar to the follwing:
update header,detail
set header.invoice = decode(header.bref,'CHANGE',971,'NEW',972,999)||header.invoice, detail.invoice = decode(header.bref,'CHANGE',971,'NEW',972,999)||detail.invoice where header.invoice = detail.invoice and header.bref is not null;Received on Sun Aug 03 1997 - 00:00:00 CEST