title: mysql update table using data from another table
body: <p>i have a table structures in my database like this:</p>
  
  <pre><code>/*city*/
  +----------+------------+
  |    id    |   name     |
  |-----------------------|
  |    1     | Gotham     |
  |    2     | Metropolis |
  |    3     | Smallville |
  |    4     | Fawcett    |
  +----------+------------+   
  
  /*district*/ 
  +----------+------------+------------+
  |    id    |   name     |   city_id  |
  |------------------------------------|
  |    1     |     A      |      1     |
  |    2     |     B      |      1     |
  |    3     |     C      |      2     |
  |    4     |     D      |      2     |
  |    5     |     E      |      2     |
  |    6     |     F      |      3     |
  |    7     |     G      |      3     |
  |    8     |     H      |      4     |
  +----------+------------+------------+
  
  /*distance*/
  +----------+-------------+------------------+-------------------------+---------+
  |    id    | origin_city | city_destination |  district_destination   |  length |
  |---------------------------------------------------------------------|---------|
  |    1     |     2       |         2        |            1            |    4    |
  |    2     |     3       |         3        |            1            |    5    |
  |    3     |     1       |         1        |            2            |    6    |
  |    4     |     2       |         2        |            3            |    5    |
  |    5     |     4       |         4        |            1            |    8    |
  |    6     |     4       |         2        |            4            |    9    |
  |    7     |     4       |         3        |            5            |    11   |
  |    8     |     1       |         4        |            6            |    13   |
  +----------+-------------+------------------+-------------------------+---------+
  </code></pre>
  
  <p>the table district is connected to city table via <strong>city_id</strong> foreign key, and the distance table is connected to both city and district table, the problem is if in distance table, there are wrong <strong>city_destination</strong> data that don't match with the <strong>district_destination</strong>, i need to fix this, but i don't know how to use the update query for this kind of trouble, to show the wrong city_destination data i used this query:</p>
  
  <pre><code>SELECT a.* FROM distance a, district b WHERE a.district_destination = b.id AND a.city_destination != b.city_id
  </code></pre>