Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 to 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?

share|improve this question

5 Answers 5

up vote 10 down vote accepted
UPDATE
FROM ips
INNER JOIN country
    ON ips.iso = country.iso
SET ips.countryid = country.countryid

Using MySQL update multiple table syntax:

http://dev.mysql.com/doc/refman/5.0/en/update.html

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:

http://en.wikipedia.org/wiki/ISO_3166-1

share|improve this answer
    
What is the point of 3-letter ISO? I just have 2-letter one. –  john.locke Jul 19 '12 at 13:26
    
@john.locke What about this one you are using in ips: iso varchar(3) DEFAULT NULL - what is it actually holding? it can hold up to 3. –  Cade Roux Jul 19 '12 at 13:30
    
@john.locke The point of the alpha-3 codes is that "three-letter country codes which allow a better visual association between the codes and the country names than the alpha-2 codes." (from the wikipedia reference) I'm not advocating anything, I'm just saying check your datatypes or your data, because the mismatch smells funny in light of the fact that there are two distinct code sets out there in the world. –  Cade Roux Jul 19 '12 at 13: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.

share|improve this answer
2  
This answer should be upvoted much more than just by me –  Peeyush Kushwaha Jun 19 '13 at 15:00
    
Thanks it works.. –  Mansoorkhan Cherupuzha Jun 23 at 6:12

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

update ips set countryid=(select countryid from country where ips.iso=country.iso )
share|improve this answer
1  
Table ips has no countryid column. How can this ever work? –  ypercube Jul 19 '12 at 13:27
2  
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 Jul 19 '12 at 13:28
1  
@ypercube I'm assuming he's adding that column with an ALTER TABLE separate from the schemas he posted. –  Cade Roux Jul 19 '12 at 13:29
    
@ypercube, read the whole question, please. –  john.locke Jul 19 '12 at 13:31
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 Jul 19 '12 at 13:34

This syntax might be better readable

UPDATE country p, ips pp
SET pp.countryid = p.countryid
WHERE pp.iso = p.iso
share|improve this answer
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Leigh Riffel Jan 4 '13 at 13:28
1  
I like this synatax as well, makes it easier to add on additional where clause items. –  Bob Herrmann Jul 26 '13 at 15:10

You can use something like this

UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.[column1_name]  t2.[column1_name] SET t1.[column2_name] = t2.[column2_name];

for detailed tutorial http://voidtricks.com/mysql-inner-join-update/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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