Thread: Merging tables

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0

    Merging tables


    Hi there, sorry new to mysql and have a question.

    I have to tables both with the same column names and structure that i want to merge into one.

    Table A has some infomation that is the same in Table B.

    How do i merge Table B into Table A without having duplcate data?

    Ive tried this.

    insert into tableA select * from tableB;

    but i get dupe information
    (example in the part no column ill have two 20002 after the merge cause it was in both tables originally)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Do these tables have primary keys? What version of MySQL are you using?
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0
    no neither tables have unique field, and the lastest version of mysql (im pretty sure ne way)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    You can try this... It is untested but I think it will get you close.

    Adam

    Code:
    insert into tableA 
    select b.*
    from tableB b natural left outer join tableA a
    where a.col1 is null
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0
    Originally posted by abombss
    You can try this... It is untested but I think it will get you close.

    Adam

    Code:
    insert into tableA 
    select b.*
    from tableB b natural left outer join tableA a
    where a.col1 is null
    okay so
    TableA= rocket
    TableB- russell2
    .col1 = partno (name of column on) right?

    i should get

    insert into rocket
    select *
    from russell2 b natural left outer join rocket a
    where a.partno is null
    ?

    i know i have inturpreted this wrong cause im new, apoligies in advance.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    He is going to need primary keys to ensure that duplicate values are not inserted.
    ...
    What column is shared between these two tables?
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0
    both tables have

    Partno | descrtion | comment
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    If the combination of these three columns is unique you could create an unique index on both tables and create a temporary table that has the difference of table1 and table2
    insert
    into table1
    select *
    from table2 t2, table1 t1
    where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3
    ...
    Try this out and let us know.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0
    Code:
    insert
    into rocket
    select *
    from russell2 t2, rocket t1
    where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3
    error:

    SQL-query :

    INSERT INTO rocket SELECT * FROM russell2 t2, rocket t1 where t2.col1 <> t1.col1 and t2.col2 <> t1.col1 and t2.col3 <> t1.col3

    MySQL said:


    INSERT TABLE 'rocket' isn't allowed in FROM table list
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    The newer version of mysql will let you do this.

    The natural left outer join does a left outer join on the columns in the two tables with the same name. It is just a shortcut. The <> is not the proper way to do the join.

    Here are a couple more ways.
    Code:
    create table temp1 select * from rocket;
    
    truncate table temp1;
    
    create unique index on temp1 (col1, col2, col3);
    
    insert ignore temp1
    select *
    from rocket;
    
    insert ignore temp1
    select *
    from russell2;
    Code:
    create table temp1 select * from rocket;
    
    truncate table rocket;
    
    insert into rocket
    select * from russell2
    union select * from temp1;
    Run each query seperatly do not run a batch, the truncate command will kill all the data in the rocket table so be careful!

    If that doesn't work... your version of mysql does not support unions yet which sucks! Then try this.
    Code:
    create table temp1 select * from rocket;
    
    truncate table rocket;
    
    insert into rocket
    select a.* from temp1 a natural inner join russell2 b;
    
    insert into rocket
    select a.* from temp1 a natural leftr outer join russell2 b
    where b.col1 is null and b.col2 is null and b.col3 is null;
    
    insert into rocket
    select b.* from temp1 a natural leftr outer join russell2 b
    where a.col1 is null and a.col2 is null and a.col3 is null;
    Good Luck... If this works please add a primary key to the table which would make this whole thing ten times easier.

    Adam
    Last edited by abombss; January 21st, 2004 at 01:04 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    You failed to read my post properly and I failed to explain clearly.
    create a temporary table that has the difference of table1 and table2
    ...
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  22. #12
  23. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    8
    Rep Power
    0
    Originally posted by victorpendleton
    You failed to read my post properly and I failed to explain clearly.

    ...
    No you explained clearly it was my stupidty that made the mistake

    I would like to thank both abombss and victorpendleton for your help. Both ways worked and the support was great!

    thanks once again ( I have a paid help service that wasnt as good as this ).
    Last edited by Hypex; January 21st, 2004 at 04:12 PM.

IMN logo majestic logo threadwatch logo seochat tools logo