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)

| Bob | newhart | | data1 |

is red part actualy null or what ?

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.

so, you would make index on target table (table1) on FirstN and LastN, then


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.

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.


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?

Assuming that you DO NOT want the magenta value to change the existing value, it's particularly easy with MySQL.


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.

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.

Actually, if you *ALWAYS* want to overwrite, so long as the table2 data is NOT null, that is equally easy! Just reverse the ifnulls:

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

No no...he can't use INSERT!


What if tab2 has Bill Gates and tab1 not ?

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.

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.

(-:

Well, yes, but your English is more precise than his was. <grin style="maximum!" />