1
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`

Results in

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM   
   cities_extended
JOIN
    cities
ON
    cities_extended.`state_id' at line 5

giving this error can you help me

4

MySQL's syntax for UPDATE does not have FROM (you probably got the syntax from a SQL Server example).

The syntax in MySQL is:

UPDATE 
     <table_references>   -- this can be multiple tables joined 
                         -- with the JOIN .. ON syntax
SET 
     column_name_1 = value1,
     column_name_2 = value2
WHERE 
     <conditions>
... ;

Try this:

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

or with table aliases:

UPDATE
        cities_extended AS ce
    INNER JOIN
        cities AS c
    ON
        ce.state_id = c.state_id
    AND
        ce.city = c.city
SET
    ce.city_id = c.id ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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