Re: sql*plus question

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
>
If you have a foreign-key constraint on the relationship, then you can only do it by creating a copy of the invoice record with the new key, then modifying all the appropriate detail records, then deleting the original invoice.

This can still all be done in one pass, using a cursor loop in PL/SQL, see following example code (insert your own column names).

DECLARE Cursor head_cur IS

    SELECT * FROM headers

     WHERE bref= 'CHANGE'
     FOR UPDATE OF invoice;

BEGIN    FOR head_rec IN head_cur LOOP

      INSERT INTO headers (invoice,xxx,yyy,etc)
      VALUES ('971'||head_rec.invoice,head_rec.xxx,head_rec.yyy,etc);

      MODIFY details
      SET invoice = '971'||head_rec.invoice         
      WHERE invoice = head_rec.invoice;

      DELETE FROM headers
      WHERE CURRENT OF head_rec;

  END LOOP; END;

--
The above posting represents the personal opinions of the author and
is not to be taken as official (or unofficial) policy or opinions of 
his employer.

Remove XSPAM from mail_id if replying via email.
Alex Heney, Living in the Global Village.