时间戳

首页 > 解决方案 > INSERT into Mysql table from table2, comparing variables in both tables

mysql - INSERT into Mysql table from table2, comparing variables in both tables

问题描述

Ok, I have two tables, Region and Cities and I need to link them together using the id from regions. At present a common code is used which does not work for my use case.

Region Table:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| country_code | varchar(255) | NO   | MUL | NULL    |                |
| code         | varchar(255) | NO   |     | NULL    |                |
| name         | varchar(255) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

City Table

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| country_code | varchar(255)     | NO   | MUL | NULL    |                |
| name         | varchar(255)     | NO   |     | NULL    |                |
| state        | varchar(255)     | NO   |     | NULL    |                |
| ascii_name   | varchar(255)     | NO   |     | NULL    |                |
| latitude     | double           | NO   |     | 0       |                |
| longitude    | double           | NO   |     | 0       |                |
| timezoneid   | varchar(255)     | NO   |     | NULL    |                |
| region_id    | int(11)          | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

In the City table, I have added the int region_id and I would like to populate it from the region table based on some city.country_code = region.country_code AND region.code = city.state.

Something along these lines:

INSERT INTO city (region_id) SELECT id FROM region WHERE city.country_code = region.country_code AND city.state = region.code;

That's the essence of what I am trying to do, MySql work is not a strong point.

Any help, appreciated!

标签： mysqlsqljoinselectsql-update

解决方案

我认为您想要一个update语句（修改现有行上的数据）而不是一个insert（创建新行）。

如果是这样，您可以使用 MySQL 的update ... join语法：

update city c
inner join region r 
    on  c.country_code = r.country_code 
    and c.state = r.code
set c.region_id = r.id

推荐阅读