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.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts

    merging table 2 into table 1 where matching files found

    I'm having a hard time finding a lesson online for what I want. I have two tables with matching columns. I'm trying to figure out what to write to merge table 2 into table 1. I want to find entries with matching first and last names, then updates missing information. I don't want to lose anything that's already in table one. Table two won't have the matching unique id's. Just the first and last name.

    Table one:
    id | firstN | lastN | height | weight | etcetera1 | etcetera2
    -- ----------------------------------------------------------------
    1 | Bob | newhart | 5ft | | 2data 2
    2 | john | cline | 5ft | | 4data 2
    1 | ed | clover | 5ft | data1 |

    Table two:

    id | firstN | lastN | height | weight | etcetera1 | etcetera2
    -- -------------------------- -------------------------------------
    | Bob | newhart | | data1 | 2data 2
    | john | cline | | data1 | 4data 2
    | ed | clover | | |


    So for example, I want Bob Newhart and John cline to update the weight category. But I don't want table 2's empty fields under height to overwrite the filled fields in table 1.

    (sorry that my sample tables are screwy looking, I didn't think about empty spaces collapsing)
    Last edited by turpentyne; 10-04-2011 at 05:40 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    | Bob | newhart | | data1 |
    is red part actualy null or what ?
    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

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by BubikolRamios View Post
    is red part actualy null or what ?
    sorry, for not clarifying. No.

    The empty space between "newhart" and "data1" in my bad example is null.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    so, you would make index on target table (table1) on FirstN and LastN, then

    Code:
    insert ... into tab1 (select * from tab2) on duplicate key update
     height= CASE WHEN height null 
                                        THEN tab2.height ELSE null END;
    Totaly untested, just to give you idea.
    Last edited by BubikolRamios; 10-04-2011 at 10:54 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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    No no...he can't use INSERT!

    He needs to use UPDATE. Just as he said.

    Turpentyne: To make your table look right in the forums, just wrap in [ code ]...[ /code] tags (without the spaces) same as you would code.

    Code:
    Table one:
    id | firstN | lastN   | height | weight | etcetera1 | etcetera2
    -- ----------------------------------------------------------------
    1 | Bob     | newhart | 5ft    |        | 2data 2
    2 | john    | cline   | 5ft    |        | 4data 2
    1 | ed      | clover  | 5ft    | 122    |
    
    Table two:
    
    id | firstN | lastN   | height | weight | etcetera1 | etcetera2
    -- -------------------------- -------------------------------------
    7  | Bob    | newhart |        | 144   | 2data 2
    8  | john   | cline   |        | 101   | 4data 2
    9  | ed     | clover  |        | 133   |
    Okay, I get that you want to copy the data in red there to table 1.

    But what about the data in magenta? It doesn't match the value in table 1. Should it update table1 or should table1's "122" value be left alone?
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    Assuming that you DO NOT want the magenta value to change the existing value, it's particularly easy with MySQL.

    Code:
    UPDATE table1, table2
    SET table1.height  = IFNULL(table1.height, table2.height),
        table1.weight  = IFNULL(table1.weight, table2.weight),
        table1.another = IFNULL(table1.another, table2.another)
    WHERE table1.firstN = table2.firstN AND table1.lastN = table2.lastN
    Voila.
    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.

  • #7
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Cool! I'm going to try this out right now. I think I'd prefer to keep it simple and safe, by not overwriting anything that might be in table 1 already.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    Actually, if you *ALWAYS* want to overwrite, so long as the table2 data is NOT null, that is equally easy! Just reverse the ifnulls:
    Code:
    UPDATE table1, table2
    SET table1.height  = IFNULL(table2.height, table1.height),
        table1.weight  = IFNULL(table2.weight, table1.weight),
        table1.another = IFNULL(table2.another, table1.another)
    WHERE table1.firstN = table2.firstN AND table1.lastN = table2.lastN
    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.

  • #9
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by Old Pedant View Post
    No no...he can't use INSERT!
    What if tab2 has Bill Gates and tab1 not ?
    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

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    Quote Originally Posted by BubikolRamios View Post
    What if tab2 has Bill Gates and tab1 not ?
    Sure, then you need an INSERT. But read his first post again. He never mentions that possibility:
    I want to find entries with matching first and last names, then updates missing information
    You just solved a different problem, not the one he asked about.
    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.

  • #11
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    From first post.
    merge table 2 into table 1
    I checked merge, by Google translate and it tells me that merge means fuse them into one.

    (-:
    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

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    Well, yes, but your English is more precise than his was. <grin style="maximum!" />
    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.


  •  

    Posting Permissions

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