I have two tables I want to merge:
city1(id, city, state, zip, lat, lon, county)
city2(id, city, state)
The tables contain overlapping
state, but not primary keys. I can't figure out how to do this.
UNION won't work, nor
INSERT IGNORE due to the different column numbers and primary keys. So, I guess that leaves joins(left?), but I can't figure out the syntax to get it it excluding duplicates.
UNION will still display duplicate city/state combinations. From what I've read,
UNION does prevent duplicates if the tables have different sizes.
Table Create Statements
Column and table names have changed
CREATE TABLE `cities` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `state_code` char(2) NOT NULL, PRIMARY KEY (`id`), KEY `idx_state_code` (`state_code`) ) ENGINE=InnoDB AUTO_INCREMENT=29739 DEFAULT CHARSET=latin1 CREATE TABLE `cities_extended` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `state_code` char(2) NOT NULL, `zip` int(5) unsigned zerofill NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, `county` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=41756 DEFAULT CHARSET=latin1