title: SQL: Reference Column Value in Subquery and Vice Versa
body: <p>Lets say I am working on a query that looks something like this:</p>
  
  <pre><code> update user as u 
     set u.city = (
      select min(c1.id) 
         from (
           select c1.id 
             from city c1 
            where c1.name = (
              select c2.name 
                from city c2 where c2.id = u.city)) as duplicates);
  </code></pre>
  
  <p>Which is meant to dedupe the city list in my user table to have the smallest city id in the city table of all id's which share the same city name.  Hence the deduping. However, I get this error:</p>
  
  <pre><code>ERROR 1054 (42S22): Unknown column 'u.city' in 'where clause'
  </code></pre>
  
  <p>My user table definitely does have a city column, ie</p>
  
  <pre><code>mysql&gt; describe user;
  +----------------------------+--------------+------+-----+---------+----------------+
  | Field                      | Type         | Null | Key | Default | Extra          |
  +----------------------------+--------------+------+-----+---------+----------------+
  | id                         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
  | first_name                 | varchar(64)  | YES  |     | NULL    |                |
  | last_name                  | varchar(64)  | YES  |     | NULL    |                |
  ...
  | city                       | bigint(20)   | YES  | MUL | NULL    |                |
  ...
  +----------------------------+--------------+------+-----+---------+----------------+
  36 rows in set (0.00 sec)
  </code></pre>
  
  <p>So I see two interestng things about this query, the user table city column value (u.city) is referred to in the subquery, and the city id from the city table is used in the middle query, (select min(c1.id)).  How do I make this work? </p>