How to update one table based on another table's values on the fly?

by ALH   Last Updated September 06, 2018 08:06 AM

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?

Tags : mysql update


Answers 6


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.

Cade Roux
Cade Roux
July 19, 2012 13:20 PM

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

update ips set countryid=(select countryid from country where ips.iso=country.iso )
ALH
ALH
July 19, 2012 13:24 PM

This syntax might be better readable

UPDATE country p, ips pp
SET pp.countryid = p.countryid
WHERE pp.iso = p.iso
SebastianLasse
SebastianLasse
January 04, 2013 09:46 AM

@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.

linuxatico
linuxatico
March 14, 2013 09:21 AM

UPDATE [table_name] AS T1,(SELECT [column_name] 
FROM [table_name] 
WHERE [column_name] = [value]) AS T2 
SET T1.[column_name]=T2.[column_name] + 1  WHERE T1.[column_name] = [value];
bhavik
bhavik
November 19, 2016 13:33 PM

What I have observed works in MySQL is the below format:

UPDATE t1
set t1.emp=t2.emp
  FROM dbo.mProd AS t1
  INNER JOIN dbo.bProd AS t2
  ON t1.ProductID = t2.ProductID
Sahanawaz
Sahanawaz
September 06, 2018 07:15 AM

Related Questions


Updates with many rows and lock escalation

Updated November 07, 2018 16:06 PM

Scrambling PII data in CLOB columns

Updated September 11, 2018 07:06 AM


Update Column in table on midnight daily

Updated April 30, 2015 23:02 PM