Geek Answers Handbook

Mysql updates all rows based on selection from another table

I have two tables:

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 the country code using the ip address from the first table:

 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 REMOTE_ADDR (IP address), but all COUNTRY_CODE entries are empty. Now I want to populate COUNTRY_CODE correctly using the ipinfo table. How can i do this?

thanks!

+8
mysql select
source share
2 answers

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 this does not comply with the statement that the column types must match, you will have to modify the logs.logs table so that the REMOTE_ADDR column is the same type (varchar (20)) as the ip_cidr table.

+9
source share

In a single point update, you use update t1 set c1=x where y .

In an update with multiple tables, you use 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

What you are looking for is something like the lines (edited) 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, max () in the original answer that I provided could not work. The above request should, although it is likely to be less efficient than something like the approach in the answer below.

+8
source share

More articles:


All Articles