Re: sql*plus question

From: Narayanan Olagappan <narayan_at_sprynet.com>
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

Original text of this message