Moving mysql columns to another table

I have a mysql issue. Hope one of you c开发者_如何学Goan help me!

I have two tables with same length and I would like to move the table_2's columns next to table_1's columns by matching the unique id (u_id).

Table structures that I have;

table_1

id - u_id - field_1 - field_2
1  - 123  - abc     - (empty)
2  - 456  - fgd     - (empty)

table_2

id - u_id - field_2
1  - 123  - kjh    
2  - 456  - mnb   

Table structures that I want;

table_new

id - u_id - field_1 - field_2
1  - 123  - abc     - kjh
2  - 456  - fgd     - mnb

How can I do this?

Thanks...

At mysql prompt:

INSERT INTO table_new (id, u_id, field_1, field_2) SELECT t1.id, t1.u_id, 
    t1.field_1, t2.field_2 FROM table_1 t1 JOIN table_2 t2 ON (t1.u_id = t2.u_id);

or just

UPDATE table_1 SET field_2 = (SELECT t2.field_2 FROM table_2 t2 WHERE t2.u_id = table_1.u_id)

I would use the multi-table UPDATE syntax.

UPDATE table1 JOIN table2 USING (u_id)
SET table1.field_2 = table2.field_2;

Straightforward enough to do it in php. Here is some pseudo code to get you started.

   SELECT u_id, field_1 FROM table_1;
   (while results)
   {
            SELECT field_2 FROM table_2 WHERE u_id = ?;

              INSERT INTO table_new VALUES (u_id, field_1, field_2);

  }

First, create table_new. Then, use the MySQL SELECT INTO. Maybe something like this:

INSERT INTO table_new SELECT table_1.u_id, table_1.field_1, table_2.field_2 
FROM table_1 LEFT JOIN table_2 USING (u_id);

create table table_new as select t1.id, t1.u_id, t1.field_1, t2.field2 from table_1 t1 left join table_2 t2 on t1.u_id = t2.u_id;

Assuming you have a php function called query which you normally use for select statements:

$tables = array();
$tables[] = query("DESCRIBE table_1");
$tables[] = query("DESCRIBE table_2");

$sql = "CREATE TABLE `table_new` {";

foreach ($tables as $table) {
  foreach ($table as $col) {
    $sql .= "`".$col['Field']."` ".$col['Type'];
    if ($col['Null'] == "NO") $sql .= " NOT ";
    $sql .= " NULL";
    if ($col['Default'] != "NULL") $sql .= " DEFAULT '".$col['Default']."'";
    $sql .= ", ";
  }
}

$sql .= ")";

That's the basic idea.. if you need to maintain foreign keys or triggers you'll need to start looking in the information_schema.statistics table