How to update one table based on another table's values on the fly?
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
countryidfield 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:
ips.isois 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?
UPDATE ips INNER JOIN country ON ips.iso = country.iso SET ips.countryid = country.countryid
Using MySQL update multiple table 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:
The join condition
USING (iso)instead of
ON ips.iso = country.isoworks too.
thanks @Cade, but I found a simple solution for it:
update ips set countryid=(select countryid from country where ips.iso=country.iso )
There is a difference in behavior to my version - your version will set it to NULL if it's not found, mine will not alter an existing value if it's not matched. This may or may not be desirable. Also the execution plan may differ depending upon the optimizer.