...

View Full Version : merging table 2 into table 1 where matching files found



turpentyne
10-04-2011, 05:38 PM
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)

BubikolRamios
10-04-2011, 05:50 PM
| Bob | newhart | | data1 |

is red part actualy null or what ?

turpentyne
10-04-2011, 07:19 PM
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.

BubikolRamios
10-04-2011, 10:52 PM
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.

Old Pedant
10-05-2011, 01:13 AM
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?

Old Pedant
10-05-2011, 01:18 AM
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.

turpentyne
10-05-2011, 01:31 AM
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.

Old Pedant
10-05-2011, 01:41 AM
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

BubikolRamios
10-05-2011, 06:51 AM
No no...he can't use INSERT!


What if tab2 has Bill Gates and tab1 not ?

Old Pedant
10-05-2011, 07:10 PM
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.

BubikolRamios
10-05-2011, 07:18 PM
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.

(-:

Old Pedant
10-05-2011, 09:40 PM
Well, yes, but your English is more precise than his was. <grin style="maximum!" />



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum