title: update column with values obtained from joining two tables in MySQL
body: <p>There's a lot of info about joining two tables, however I'm not interested in a Select, but an Update (this one also didn't help: <a href="https://stackoverflow.com/questions/3302221/update-a-column-in-a-table-with-values-from-two-other-tables">Update a column in a table with values from two other tables</a>).</p>
  
  <p>I need to order attributes in Magento. The way I'm planning to do it is this:
  There are two relevant tables (eav_attribute_option and eav_attribute_option_values). They are joined by <strong>option_id</strong>. 
  I need to update the <strong>sort_order</strong> field in the <strong>eav_attribute_option</strong> table according to the alphabetical order in the <strong>eav_attribute_option_values</strong> table (<strong>value</strong> column).</p>
  
  <p>What I have so far is this:</p>
  
  <pre><code>SELECT *
  FROM eav_attribute_option_value, eav_attribute_option
  WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id    
  ORDER BY value  
  </code></pre>
  
  <p>with which I obtain:</p>
  
  <pre><code>value_id option_id store_id value    option_id_1  attribute_id  sort_order
  13534      5681     0   ADULT               5681          131           0
  13543      5710     0   Yellow              5710          134           0
  13547      502      0   Yellow - 10A         502          127           0
  13548      3001     0   Yellow - 120cm      3001          127           0
  13549      503      0   Yellow - 12A         503          127           0
  </code></pre>
  
  <p>with the following query I see what I need:</p>
  
  <pre><code>SELECT t.*, @i:=@i+1 AS iterator
  FROM (SELECT value, eav_attribute_option.option_id
  FROM eav_attribute_option_value, eav_attribute_option
  WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id 
  ORDER BY value) t,(SELECT @i:=0) foo
  </code></pre>
  
  <p>I get this:</p>
  
  <pre><code>value           option_id   iterator
  ADULT           5681        1
  Yellow          5710        2
  Yellow - 10A    502         3
  Yellow - 120cm  3001        4
  Yellow - 12A    503         5
  Yellow - 14A    504         6
  </code></pre>
  
  <p>The big question:
  <strong>How can I update the sort_order column in eav_attribute_option with the values from the "iterator" column, having option_id as the linking field?</strong></p>
  
  <h1>    select * from  eav_attribute_option</h1>
  
  <pre><code>option_id   attribute_id    sort_order
  1            18             0
  2            18             1
  3            127            0
  4            127            0
  5            127            0
  6            127            0
  </code></pre>
  
  <p>I got the "iterator" query from here: <a href="https://stackoverflow.com/questions/3126972/mysql-row-number">With MySQL, how can I generate a column containing the record index in a table?</a></p>
  
  <p>edit: Here's the answer</p>
  
  <pre><code>UPDATE eav_attribute_option, (SELECT t.*, @i:=@i+1 AS iterator 
  FROM 
  (SELECT value, eav_attribute_option.option_id 
      FROM eav_attribute_option_value, eav_attribute_option 
      WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id 
      ORDER BY value) t,(SELECT @i:=0) x) tbl2 
  SET eav_attribute_option.sort_order = tbl2.iterator 
  WHERE eav_attribute_option.option_id = tbl2.option_id 
  </code></pre>