CREATE TABLE `cities` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables


-- --------------------------------------------------------

--
-- Table structure for table `cities_extended`
--

CREATE TABLE `cities_extended` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` int(5) UNSIGNED ZEROFILL NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities_extended`
--
ALTER TABLE `cities_extended`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cities_extended`
--
ALTER TABLE `cities_extended`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

UPDATE
    cities_extended
SET
    cities_extended.`city_id` = cities.`id`
FROM
   cities_extended
INNER JOIN
    cities
ON
    cities_extended.`state_id` = cities.`state_id`
AND
    cities_extended.`city`= cities.`city`