MySQL Mysql根据从另一个表中选择更新所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4817004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql update all rows based on select from another table
提问by anon
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!
谢谢！
回答by Dean Rather
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))。
回答by nicolaskruchten
In a single-table update you use update t1 set c1=x where y.
在单表更新中，您使用update t1 set c1=x where 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 where 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
您正在寻找的是（已编辑） 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
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() 无法工作。上面的查询应该，尽管它可能比下面提供的答案中的方法效率低。
