title: MySQL UPDATE with SUBQUERY of same table
body: <p>I am working with a complex MySQL database table that collects form data. I have simplified the layout in an example table called <strong>test</strong> below:</p>
  
  <pre><code>|FormID|FieldName|  FieldValue |
  |   1  |   city  |   Houston   |
  |   1  | country |     USA     |
  |   2  |   city  |   New York  |
  |   2  | country |United States|
  |   3  | property|   Bellagio  |
  |   3  |  price  |     120     |
  |   4  |   city  |   New York  |
  |   4  |zip code |    12345    |
  |   5  |   city  |   Houston   |
  |   5  | country |     US      |
  </code></pre>
  
  <p>Through phpMyAdmin I need to make global updates to some tables, specifically I want to <em>update</em> all <strong>FieldValue</strong> entries to "United States of America" with the <strong>FieldName</strong> "country" that have the same <strong>FormID</strong> as the <strong>FieldName</strong> "city" and the <strong>FieldValue</strong> "Houston".</p>
  
  <p>I can easily <em>display</em> these entries with a SELECT statement by either using a SUBQUERY or by using an INNER JOIN:</p>
  
  <pre><code>SELECT FieldValue
  FROM test
  WHERE FormID
  IN (
     SELECT FormID
     FROM test
     WHERE FieldName =  "city"
     AND FieldValue =  "Houston"
     )
  AND FieldName =  "country"
  </code></pre>
  
  <p>Or:</p>
  
  <pre><code>SELECT a.FieldValue
  FROM test a
  INNER JOIN test b ON a.FormID = b.FormID
  WHERE a.FieldName = "country"
  AND b.FieldName = "city"
  AND b.FieldValue = "Houston"
  </code></pre>
  
  <p>However I try to compose my <strong>UPDATE</strong> statement I get some form of MySQL-error indicating that I cannot reference the same table in either a <strong>subquery</strong> or <strong>inner join</strong> or <strong>union</strong> scenario. I have even created a <strong>view</strong> and tried to reference this in the update statement, but no resolve.
  Does anyone have any idea how to help me?</p>