I want to join 2 tables, in order to insert A table(stats_country_index)'s id-nation to B table
(stats_country_ip_index)'s id_nation column, which is now blank.
I would be much appreciated it if you would make proper query sentence.
For your ref., my query sentence may be wrong and has no response.
- Code: Select all
UPDATE stats_country_index A, stats_country_ip_index B SET B.id_nation = 'A.id_nation'
WHERE A.countrycode = 'B.countrycode';
A table
CREATE TABLE IF NOT EXISTS `stats_country_index` (
`id_nation` smallint(6) NOT NULL auto_increment,
`countrycode` varchar(10) NOT NULL,
`countrycode2` varchar(10) NOT NULL default '',
`countryname` varchar(60) NOT NULL default '',
PRIMARY KEY (`id_nation`),
KEY `countrycode` (`countrycode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `stats_country_index` (`id_nation`, `countrycode`, `countrycode2`, `countryname`) VALUES
('1', 'AF', 'AFG', 'Afghanistan\r'),
('2', 'AL', 'ALB', 'Albania\r'),
('3', 'DZ', 'DZA', 'Algeria\r'),
('4', 'AD', 'AND', 'Andorra\r'),
('5', 'AO', 'AGO', 'Angola\r');
B table
CREATE TABLE IF NOT EXISTS `stats_country_ip_index` (
`ipfrom` varchar(30) NOT NULL default '',
`ipto` varchar(30) NOT NULL default '',
`id_nation` varchar(60) NOT NULL,
`countrycode` varchar(60) NOT NULL default '',
KEY `id_nation` (`id_nation`(6))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `stats_country_ip_index` (`ipfrom`, `ipto`, `id_nation`, `countrycode`) VALUES
(' 33996344 ', ' 33996351 ', '', ' GB '),
(' 50331648 ', ' 69956103 ', '', ' US '),
(' 69956104 ', ' 69956111 ', '', ' BM '),
(' 69956112 ', ' 83886079 ', '', ' US '),
(' 94585424 ', ' 94585439 ', '', ' SE ');