Update with joining another table 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
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 
     &lt;table_references&gt;   -- this can be multiple tables joined 
                         -- with the JOIN .. ON syntax
SET 
     column_name_1 = value1,
     column_name_2 = value2
WHERE 
     &lt;conditions&gt;
... ;


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 ;