mysql - SQL更新表1使用表2 UPDATE FROM SELECT

我正在尝试更新table1的country_id，因此我可以删除列iso_2_code和Country，因为此数据在db中是多余的，并且已经存储在table2中

表1-oc_ip_geo_table

=================================================================================================
|   ID  |    StartRange     |   EndRange        |   iso_2_code  |   Country     |   country_id  |
=================================================================================================
|  1    |    1.0.1.0        |   1.0.3.255       |       CN      |   China       |       0       |
|  2    |    1.0.4.0        |   1.0.7.255       |       AU      |   Australia   |       0       |
|  3    |    1.0.8.0        |   1.0.15.255      |       CN      |   China       |       0       |
|  4    |    1.0.16.0       |   1.0.31.255      |       JP      |   Japan       |       0       |
|  ...  |    ...            |   ...             |       ...     |   ...         |       ...     |
| 87035 |    223.255.255.0  |   223.255.255.255 |       AU      |   Australia   |       0       |
-------------------------------------------------------------------------------------------------

表2-oc_country

=================================================================================
|   country_id      |   name            |   iso_code_2  |   *   |   *   |   *   |
=================================================================================
|       1           |   Afghanistan     |       AF      |   *   |   *   |   *   |
|       2           |   Albania         |       AL      |   *   |   *   |   *   |
|       3           |   Algeria         |       DZ      |   *   |   *   |   *   |
|       ...         |   ...             |       ...     |   ... |   ... |   ... |
|       251         |   Canary Islands  |       IC      |   *   |   *   |   *   |
---------------------------------------------------------------------------------

搜索到google结果后，我发现我的查询看起来应该像这样。但似乎不起作用。

UPDATE oc_ip_geo_table
SET oc_ip_geo_table.country_id = oc_country.country_id
FROM oc_ip_geo_table
INNER JOIN oc_country
ON oc_ip_geo_table.iso_2_code = oc_country.iso_code_2

谁能帮助我进行正确的查询

最佳答案

好的，看来我在此之前发布的查询几乎是正确的，但是更改一点就解决了它

UPDATE oc_ip_geo_table AS t1
SET t1.country_id =
    ( SELECT t2.country_id
      FROM oc_country AS t2
      WHERE t2.iso_code_2 = t1.iso_2_code

    )

改变成

UPDATE oc_ip_geo_table AS t1
SET t1.country_id =
    ( SELECT t2.country_id
      FROM oc_country AS t2
      WHERE t1.iso_2_code = t2.iso_code_2

    )

关于mysql - SQL更新表1使用表2 UPDATE FROM SELECT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25764740/