1

I would like to update cities_extended.city_id based on a join with cities

Table cities

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;

ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

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;

ALTER TABLE `cities_extended`
  ADD PRIMARY KEY (`id`);

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

Update command

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

5

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.