Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Angry Merge 2 Tables from different databases under the same user

    Hi guys, I am stucked here... I need help please..

    I dont know why I am getting this error, I think this morning i ran this query without errors and it worked.. I am trying to merge two different tables from two different databases under the same user into one table, the tables have the same fields, but different values, and I want a big table with the fields from the 2 merged tables.
    I am using:

    CREATE TABLE students_merged engine = MERGE UNION = (db1.students_contacts,db2.students_contacts);

    And i get

    "#1113 - A table must have at least 1 column "

    Do you know what I am doing wrong, please?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    http://dev.mysql.com/doc/refman/5.1/...ge-engine.html:
    Code:
     CREATE TABLE total (
        ->    a INT NOT NULL AUTO_INCREMENT,
        ->    message CHAR(20), INDEX(a))
        ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
    Newer used that, but looks like you have to state columns of that new table.
    And you did not specify any.

    anyway I would do something like this:

    Code:
    create table  students_merged as select * from db1.students_contacts;
    insert ignore into students_merged select * from db2.students_contacts;
    Last edited by BubikolRamios; 03-19-2013 at 02:36 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    lorenzofln (03-19-2013)

  • #3
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    hello again, thanks for your help, please is there any way to include a order by field (date) when merging the tables and also a way to run this query only to update the merged tables with new records from the other two tables? please help me, thank you so much.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    order by when merging is pointles, you can always do order by when geting data from merged table

    Code:
    only to update the merged tables
    insert ignore into
    The key here is ignore keyword.

    As per key in your merged table, which should be the same as in source tables, by my recipie, duplicates(that is, records that you already imported)
    will be ignored.

    so you would do, to do update only

    Code:
    insert ignore into students_merged select * from db1.students_contacts;
    insert ignore into students_merged select * from db2.students_contacts;
    and run that any number of times and it will transfer only new records, that is, if you set key(index) right & up to your desires.

    If you don't understand (which I think you dont) what i'm telling you, paste here create code of students_merged table.
    Last edited by BubikolRamios; 03-19-2013 at 02:41 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Code:
    create table  students_merged as insert ignore into students_merged select * from db1.students_contacts;
    insert ignore into students_merged select * from db2.students_contacts;
    is this right??? lol

  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    No . As I said, give as your students_merged table strcture.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #7
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am sorry, I dont understand , can you give me just the code? Please..

  • #8
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Well it is like this, in order to ask me a question about potatoes you must know ,approximately at least,what potatoes are, right ?

    So get forinstance this: http://www.heidisql.com/download.php

    with that you will see your db & tables & specific table create code.

    Without that, I can write a novel about your problem with no usable result whatsoever .
    Last edited by BubikolRamios; 03-19-2013 at 05:44 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #9
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    hello my friend, yes you are right.. I saw now..

    well, i understood that the main problem is that both databases has a column named "id" that is incremental, and when i merge them, the ID column has duplicates, and when i try to see them on our software, duplicates are ignored, so basically i see only the main table, like I didnt merged the second one..

    is there any way to merge those tables and change the incremental "id" field for the new records? i mean, if table 1 has 1000 entries, and table 2 has 200 entries, i would like table 3 to have 1200 entries each one with a unique valeu in the id field, did you understand me???? thanks in advance as ALWAYS...

  • #10
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Ok you figured you have auto inc field which is a key. I'm not a spoon bender to
    figure out what other key fields you will pull out in next zilion posts.

    You should understand one thing about this forum. It is help me understand and not do it for me.

    Anyway, I'm willing to do it for you if you provide table structure. <-- THIS IS RED FOR A REASON !
    As I told you.

    Otherwise you are wasting my(and anyone else looking at this thread) time.

    There is a hint: look at second post in this thread, there is sample create table code (in first code box)
    Last edited by BubikolRamios; 03-19-2013 at 08:12 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #11
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you my friend, I swear I am studying mysql and php... I WANT to understand.. but sometimes time is soooo short...

    let me see if this can help you.. Key and Extra columns are empty in all fields..

    Field Type Null Default
    id int(11) NO 0
    product_id int(11) NO NULL
    user_id int(5) NO NULL
    date datetime NO NULL
    name varchar(255) NO NULL
    email varchar(255) NO NULL
    phone varchar(100) NO NULL
    address varchar(255) NO NULL
    city varchar(255) NO NULL
    state varchar(255) NO NULL
    cp varchar(100) NO NULL
    text text NO NULL
    userfile varchar(255) NO NULL
    layout varchar(255) NO NULL
    Last edited by lorenzofln; 03-19-2013 at 08:40 PM.

  • #12
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    where are indexes, keys ?
    To quote again, how should create table code look like.
    CREATE TABLE total (
    -> a INT NOT NULL AUTO_INCREMENT,
    -> message CHAR(20), INDEX(a))
    -> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    So the "secret" here is to first create a table of that same structure.

    Fortunately, there is a *TRIVIAL* way to do that with MySQL:
    Code:
    CREATE TABLE students_merged LIKE db1.students_contacts;
    And then it's easy to put the first table into that:
    Code:
    INSERT INTO students_merged
    SELECT * FROM db1.students
    But then to put in the *second* table, you must specify *ALL* the fields *EXCEPT* the auto_increment field.

    Like this:
    Code:
    INSERT INTO students_merged ( product_id, user_id, ...., layout )
    SELECT product_id, user_id, ..., layout FROM db2.students
    NOW...

    (1) That will give *NEW* id values to all the students from db2.students. When you don't specify the id, and that field is auto_increment, it will just assign the next number. If that's not going to work for you--if you need to retain the old id numbers for some reason--then we have to do something different.

    (2) That all assumes that you do not need to worry about duplicates. If you do, we can resolve that, too. But what *constitutes* a duplicate, in your world? Can't help until you call that out in clear language.



    You would need to tell us in very explicit terms.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New Coder
    Join Date
    Mar 2013
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry my friend, I really want to understand, but I do not understand what you are asking me.

    Indexes, I dont know where to find indexes.

    Keys, the Keys column in mynable structure is empty, I wrote this before, all fields of the Keys column are empty in the table view.

    You asked me the table structure, right? I thought I had given you the table structure in my last post, there are no keys because they are empty and about indexes, well, I dont know where to find them.

    Please tell me exactely what you need and i will do My best..... sorry for being so hard for me to understand

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,032
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    I don't think you need to worry about keys and indexes is you use my suggestion: Creating one table LIKE another clones those, as well.

    But for reference: The best way to get a *FULL* description of a table in MySQL is to use the SHOW CREATE TABLE command.

    So if you used SHOW CREATE TABLE db1.students; that would give us all available information about the table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Tags for this Thread

    Posting Permissions

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