mysql - MySQL中根据键迁移列

Question

我需要插入combined_streets来自table1进入combined_streets在table2 。公共(public) key 是stop_id但请注意stop_id是唯一的table1但有重复stop_id在table2 。

table1

+---------+-----------+--------------+-----------+------------+-----------------+---------+---------------+----------------------+
| stop_id | on_street | cross_street | boardings | alightings | month_beginning | daytype | location      | combined_streets     |
+---------+-----------+--------------+-----------+------------+-----------------+---------+---------------+----------------------+
|     137 | JACKSON   | KOLMAR       | 4.6       | 14.2       | 10/01/2012      | Weekday | "(41.87696338 | JACKSON & KOLMAR     |
|     138 | JACKSON   | KENTON       | 5         | 15.2       | 10/01/2012      | Weekday | "(41.87696546 | JACKSON & KENTON     |
|     139 | JACKSON   | KILPATRICK   | 38.9      | 242.1      | 10/01/2012      | Weekday | "(41.87692666 | JACKSON & KILPATRICK |
|     140 | JACKSON   | CICERO       | 0.2       | 1.7        | 10/01/2012      | Weekday | "(41.87685893 | JACKSON & CICERO     |
|     141 | JACKSON   | 4900 WEST    | 0         | 0          | 10/01/2012      | Weekday | "(41.87682102 | JACKSON & 4900 WEST  |
|     142 | JACKSON   | LAVERGNE     | 0         | 0          | 10/01/2012      | Weekday | "(41.87680393 | JACKSON & LAVERGNE   |
|     143 | JACKSON   | 5056 WEST    | 0         | 0          | 10/01/2012      | Weekday | "(41.87675729 | JACKSON & 5056 WEST  |
|     144 | JACKSON   | LEAMINGTON   | 0         | 0          | 10/01/2012      | Weekday | "(41.87672858 | JACKSON & LEAMINGTON |
|     145 | JACKSON   | LARAMIE      | 59.1      | 172.9      | 10/01/2012      | Weekday | "(41.87673038 | JACKSON & LARAMIE    |
|     146 | JACKSON   | LOCKWOOD     | 14.3      | 92         | 10/01/2012      | Weekday | "(41.87667680 | JACKSON & LOCKWOOD   |
+---------+-----------+--------------+-----------+------------+-----------------+---------+---------------+----------------------+

table2

+---------+-------+------------------+
| stop_id | route | combined_streets |
+---------+-------+------------------+
|      74 | 126   | NULL             |
|      74 | 132   | NULL             |
|      74 | 143   | NULL             |
|      74 | 147   | NULL             |
|      75 | 1     | NULL             |
|      75 | 7     | NULL             |
|      75 | X28   | NULL             |
|      75 | 126   | NULL             |
|      75 | 129   | NULL             |
|      76 | 3     | NULL             |
+---------+-------+------------------+

Answer 1

UPDATE table2 t2 LEFT JOIN table1 t1 ON t2.stop_id = t1.stop_id SET t2.combined_streets = t1.combined_streets;