CODEWITHSUNDEEP
mysql
miket
miket

Reputation: 167

mysql combine two tables

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

Upvotes: 1

Views: 1840

Answers (3)

miket
miket

Reputation: 167

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?

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

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.

Upvotes: 1

user1477622
user1477622

Reputation: 132

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.)

Upvotes: 0

Related Questions