Re: [SQL] How to merge two tables into one ?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 09 Jul 1998 00:40:38 GMT
Message-ID: <35a40f90.2678461@dcsun4.us.oracle.com>

On Wed, 08 Jul 1998 12:57:00 GMT, Zahi Al-Jamous <aljamous_at_crv.pechiney.fr> wrote:

>Hi all,
>
>i have two tables structured like this :
>
>Table policy
> Id
> Number
> Customer
> Adress
>Table Commentary
> Id (foreign key references policy.Id)
> LineNumber
> Comment
>
>I want to have one table :
>table PolicyNew
> Id
> Number
> Customer
> Adress
> Comment
>
>Where "Comment" is the concatenation of all the "Comment" fields in
>table Commentary where Policy.Id = Commentary.Id
>
>Can someone help me writing this script ?

I think this is what you want...

begin

   --
   --  Move all the data from Policy to PolicyNew
   --

   insert into PolicyNew ( Id, Number, Customer, Adress )    values select id, number, customer, adress from policy;

   --
   --  Move Comments by updating Policy New
   --
   for x in ( select * from Commentary order by Id, LineNumber ) loop
      update PolicyNew
      set Comment = Comment || ' ' || x.Comment
      where id = x.id;

  end loop;
  commit;
end;
/

chris. Received on Wed Jul 08 1998 - 19:40:38 CDT