Mysql根据另一个表中的select更新所有行

I have two tabels;

我有两个表格;

mysql> describe ipinfo.ip_group_country;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip_start     | bigint(20)  | NO   | PRI | NULL    |       |
| ip_cidr      | varchar(20) | NO   |     | NULL    |       |
| country_code | varchar(2)  | NO   | MUL | NULL    |       |
| country_name | varchar(64) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

mysql> describe logs.logs;
+----------------------+------------+------+-----+---------------------+----------------+
| Field                | Type       | Null | Key | Default             | Extra          |
+----------------------+------------+------+-----+---------------------+----------------+
| id                   | int(11)    | NO   | PRI | NULL                | auto_increment |
| ts                   | timestamp  | NO   |     | CURRENT_TIMESTAMP   |                |
| REMOTE_ADDR          | tinytext   | NO   |     | NULL                |                |
| COUNTRY_CODE         | char(2)    | NO   |     | NULL                |                |
+----------------------+------------+------+-----+---------------------+----------------+

I can select country code using ip address from first table:

我可以使用第一个表中的ip地址选择国家代码:

mysql> SELECT country_code FROM ipinfo.`ip_group_country` where `ip_start` <= INET_ATON('74.125.45.100') order by ip_start desc limit 1;
+--------------+
| country_code |
+--------------+
| US           |
+--------------+

In logs.logs, I have all the REMOTE_ADDR (ip address) set, but all COUNTRY_CODE entries are empty. Now, I want to populate COUNTRY_CODE appropriately using the ipinfo table. How can I do this?

在logs.logs中,我设置了所有REMOTE_ADDR(IP地址),但所有COUNTRY_CODE条目都为空。现在,我想使用ipinfo表适当地填充COUNTRY_CODE。我怎样才能做到这一点?

thanks!

2 个解决方案

#1

Try

UPDATE logs.logs
SET COUNTRY_CODE = (
    SELECT country_code
    FROM ipinfo.ip_group_country
    WHERE ipinfo.ip_start <= INET_ATON(logs.REMOTE_ADDR)
    LIMIT 1
)
WHERE COUNTRY_CODE IS NULL

If it fails saying the column types must match, you'll have to alter your logs.logs table so that the REMOTE_ADDR column is the same type (varchar(20)) as the ip_cidr table.

如果未能说列类型必须匹配,则必须更改logs.logs表,以使REMOTE_ADDR列与ip_cidr表的类型相同(varchar(20))。

#2

In a single-table update you use update t1 set c1=x where y.

在单表更新中,您使用update t1 set c1 = x其中y。

In a multi-table update you use update t1, t2 set t1.c1=t2.c2 where t1.c3=t2.c4

在多表更新中,使用update t1,t2 set t1.c1 = t2.c2其中t1.c3 = t2.c4

Here's the relevant documentation http://dev.mysql.com/doc/refman/5.0/en/update.html

这是相关文档http://dev.mysql.com/doc/refman/5.0/en/update.html

What you're looking for is something along the lines of (editted) update logs.logs as l, ipinfo.ip_group_country as c set l.COUNTRY_CODE=c.country_code where c.ip_start <= INET_ATON(l.REMOTE_ADDR) order by c.ip_start asc

您正在寻找的内容是(编辑)更新logs.logs为l,ipinfo.ip_group_country为c set l.COUNTRY_CODE = c.country_code其中c.ip_start <= INET_ATON(l.REMOTE_ADDR)以c顺序排序.ip_start asc

Edit: you're right, the max() in the original answer I provided could not work. The query above should, although it will likely be less efficient than something like the approach in the answer provided below.

编辑:你是对的,我提供的原始答案中的max()无法正常工作。上面的查询应该是,尽管它可能不如下面提供的答案中的方法那样有效。

#1

Try

UPDATE logs.logs
SET COUNTRY_CODE = (
    SELECT country_code
    FROM ipinfo.ip_group_country
    WHERE ipinfo.ip_start <= INET_ATON(logs.REMOTE_ADDR)
    LIMIT 1
)
WHERE COUNTRY_CODE IS NULL