Results 1 to 4 of 4

Thread: merging tables

  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: merging tables

    is there any so that two tables can be merger based on the foreign key

    like parent(a(primary key) ,z) is a table
    and child(id (foreign key(a)),sal) is a table

    i want to merge the two and final table is
    parent(a,z,sal)

    i want to merge the columns of the table

    please suggest a way

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Yes, but not with a single SQL

    Code:
    --create the tables
    create table parent ( a serial not null, z int, primary key (a));     
    create table child  ( a int REFERENCES parent(a), sal char(1));
    
    --insert some stuff for test
    insert into parent values (0, 10);  
    insert into parent values (0, 20);
    insert into parent values (0, 30);  
    insert into child values (1, "N");  
    insert into child values (2, "Y");
    insert into child values (3, "Y");
    
    --Add the child columns to the parent table
    alter table parent add sal char(1);
    
    --Move the data from child to parent
    update parent set sal=(select sal from child where parent.a=child.a) where 1=1;
    
    --Check the parent table
    select * from parent;
              a           z sal
              1          10 N
              2          20 Y
              3          30 Y
              
    --drop the child table
    drop table child;
    A second way:
    Code:
    --create the tables
    create table parent ( a serial not null, z int, primary key (a));     
    create table child  ( a int REFERENCES parent(a), sal char(1));
    
    --insert some stuff for test
    insert into parent values (0, 10);  
    insert into parent values (0, 20);
    insert into parent values (0, 30);  
    insert into child values (1, "N");  
    insert into child values (2, "Y");
    insert into child values (3, "Y");
                  
    --create new parant table    
    create table parent2 (a int not null, z int, sal char(1), primary key (a));
                   
    --Move the data from child/parent to new parent
    insert into parent2 
    select     parent.a, parent.z, child.sal 
    from       parent
    inner join child
    on         parent.a=child.a;
    
    --change to serial
    alter table parent2 modify a serial not null primary key;
            
    --Check the new parent table
    select * from parent2;
              a           z sal
              1          10 N
              2          20 Y
              3          30 Y
              
    --drop the child and old parent table
    drop table child;   
    drop table parent;
    
    --rename the new parent table
    rename table parent2 to parent;

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    the table i provided is just a sample table
    the actual table is very big and we cant alter the table and adding all the column like this.

    The actual picture is i have two table (one referencing the other). So is there any way like there is merge command in informix. can that be used in this case.

    i have tried that command but did not get the desired output.

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    I think merge doesn't help you here.

    You can add one column to the parent table, drop this one from the child, add the second one....

    Unload both tables, merge the files and load that into a new table is another way to do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •