I have a table in the name of ips as below:
CREATE TABLE `ips` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`begin_ip_num` int(11) unsigned DEFAULT NULL,
`end_ip_num` int(11) unsigned DEFAULT NULL,
`iso` varchar(3) DEFAULT NULL,
`country` varchar(150) DEFAULT NULL
) ENGINE=InnoDB
Lets assume I have a countryid
field on this table from country table which is as below:
CREATE TABLE `country` (
`countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`ordering` smallint(5) unsigned NOT NULL DEFAULT '0',
`iso` char(2) NOT NULL,
PRIMARY KEY (`countryid`)
) ENGINE=InnoDB
There is about 100,000 records in ips table. Is there any query for the following scenario:
Check if ips.iso
is equal to country.iso
, if it's equal then add country.coutryid to that record. I couldn't think of any way to do it. Do you have any idea how to do that?
Best Answer
Using MySQL update multiple table syntax:
14.2.11 UPDATE Syntax
Note that you have two different lengths and data types on your iso columns. There are, in fact, two separate sets of ISO codes, 2-letter and 3-letter, so you may not in reality be able to join these columns:
ISO 3166-1
The join condition
USING (iso)
instead ofON ips.iso = country.iso
works too.