50

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?

Improve this question

4 Answers 4

Reset to default
88 
UPDATE ips INNER JOIN country
    ON ips.iso = country.iso
SET ips.countryid = country.countryid

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 of ON ips.iso = country.iso works too.

Improve this answer
3
  • You are a Genius you answer saved my time
    – humphrey
    Commented Jan 7, 2018 at 9:18
  • It amazes me how little code is necessary to perform this action!
    – Matt Cremeens
    Commented Jun 24, 2019 at 13:31
  • you saved my time. this is worked as well
    – saber tabatabaee yazdi
    Commented Jul 22, 2020 at 9:42
32

@Cade Roux's solution gives me a syntax error, the correct one for mysql 5.5.29 is:

UPDATE ips 
INNER JOIN country
    ON ips.iso = country.iso
SET ips.countryid = country.countryid

without the "FROM" keyword.

Improve this answer
1
  • 1
    It has since been fixed, seems...
    – rogerdpack
    Commented Oct 6, 2018 at 5:52
13

This syntax might be better readable

UPDATE country p, ips pp
SET pp.countryid = p.countryid
WHERE pp.iso = p.iso
Improve this answer
0
5

thanks @Cade, but I found a simple solution for it: 

update ips set countryid=(select countryid from country where ips.iso=country.iso )
Improve this answer
3
  • 5
    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.
    – Cade Roux
    Commented Jul 19, 2012 at 13:28
  • @CadeRoux I didn't think about NULL part, thanks.
    – Alireza
    Commented Jul 19, 2012 at 13:32
  • 1
    @john.locke It's probably not a problem - when you add the new column, I'm assuming it will be NULL and also a foreign key, so invalid entries wouldn't be allowed anyway. But it's hard to tell from what has been explicitly given in your question.
    – Cade Roux
    Commented Jul 19, 2012 at 13:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.