mysql combine two tables

Question

I have two tables, table_a and table_b:

table_a:

city 
--------
tokoyo 
london

table_b:

zipcode  
-------
23675
11290

How to combine table_b to table_a, like this:

table_a:

city          zipcode
--------      --------
tokoyo        23675
london        11290

Can you describe the way they are related? were they inserted in the same order? Is there a column you haven't described? — Chris Trahey, Jun 24 '12 at 05:55
@ctrahey As same as add table_a a new column called zipcode and insert data = array(23675,11290) from line 1 — miket, Jun 24 '12 at 06:00

Chris Trahey · Accepted Answer · 2012-06-24T06:45:56.013

1

Without a common column to join on, I don't think you will be able to trust the results (as far as I know, MySQL does not guarantee that rows are returned in the order they are inserted).

But to be technical, I think you can achieve this in the following way: Don't actually do this, see below

ALTER TABLE table_a ADD COLUMN zipcode unsigned int;
ALTER TABLE table_a ADD COLUMN id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL;
ALTER TABLE table_b ADD COLUMN id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL;
UPDATE table_a JOIN table_b USING(id) SET table_a.zipcode = table_b.zipcode;

Forgive me if my syntax is off, it's been a while.

This technique leverages what I think MySQL does when you add a non-null auto_increment column: it fills in the values for you, one unique value per row. Now you have a joining column, and can perform the update.

However, I'll state this again to be very clear: If there really is no additional columns between the tables, you have no completely accurate way to join these two tables. You would need either a common join column, or at least each having an auto_increment column and the guarantee that all zipcodes and cities where entered in appropriate pairs in transactions.

edited Jun 24 '12 at 06:45

answered Jun 24 '12 at 06:09

Chris Trahey

18,202
1
42
55

I test your code just now,It update from line 1,this is great!but line 1's vlaue == line 2's vlaue – miket Jun 24 '12 at 06:24
Ah, yes, because all of the rows in table_b will match each of the rows in table_a... hmmm... I have another idea, I will edit my post with it... however, please reflect on what some of us are trying to tell you... you cannot rely on the zipcode matching the city, – Chris Trahey Jun 24 '12 at 06:38
PRIMARY KEY AUTO_INCREMENT this not the solution I have the PRIMARY KEY before,but I have deleted the PRIMARY KEY – miket Jun 24 '12 at 06:52
It's nothing inherent in PK/AI... it's just getting mysql to create a column for us to join on (the column exists in both tables, and has mirrored values in each) – Chris Trahey Jun 24 '12 at 06:54
Your are right,I think this is the best way till now.It works! – miket Jun 24 '12 at 07:00

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

It would seem you did not do any research regarding your question at all. There is a slew of related questions that ask the same thing.

Join two mysql tables

This and many other links discuss your question.

(This answer would be a comment if I had enough reputation. I apologize in advance.)

edited May 23 '17 at 12:29

Community

1
1

answered Jun 24 '12 at 06:00

user1477622

132
7

1

you should have made this a comment – codingbiz Jun 24 '12 at 06:01
Before ask the question I have full searched ,I find a same question,but have no solution http://www.justskins.com/forums/adding-a-column-with-140563.html – miket Jun 24 '12 at 06:06

score 0 · Answer 3 · edited May 23 '17 at 12:14

Finally I found the solution :

>    $dbh->exec('ALTER TABLE `table_a` ADD COLUMN `zipcode` int(10) NOT NULL');
>                                                                   
>                                                                   
>    $i = 0;                                                                   
>    foreach ($dbh->query('SELECT zipcode FROM table_b') as $row) {            
>    $sql = 'UPDATE table_a SET zipcode='.$row["zipcode"].'                    
>     WHERE zipcode IN (                                                       
>         SELECT zipcode FROM (                                                
>             SELECT zipcode FROM table_a                                      
>             LIMIT '.$i.', 1                                                  
>         )  TMP                                                               
>     ) LIMIT 1;';                                                             
>    $dbh->exec($sql);                                                         
>    $i = $i +1;                                                               
>    }                                                                         
>                                                                   
>                                                                   
>                                                                   
>    $dbh->exec('ALTER TABLE `table_a` ADD COLUMN `zipcode` int(10) NOT NULL');
>                                                                   
>                                                                   
>    $zipcodes = array(23675,11290);                                           
>                                                                   
>    for ($i = 0;$i < 2;$i++){                                                 
>                                                                   
>    $sql = 'UPDATE table_a SET zipcode='.$zipcodes[$i].'                      
>     WHERE zipcode IN (                                                       
>         SELECT zipcode FROM (                                                
>             SELECT zipcode FROM table_a                                      
>             LIMIT '.$i.', 1                                                  
>         )  TMP                                                               
>     ) LIMIT 1;';                                                             
>    $dbh->exec($sql);                                                         
>                                                                   
>    }

Lukáš Lalinský's answer is marvelous :

update multiple rows using limit in mysql?

mysql combine two tables

3 Answers3